Skip navigation

Function for looking up multiple variables

707 Views 21 Replies Latest reply: Aug 6, 2012 7:30 PM by Jerrold Green1 RSS
1 2 Previous Next
gzcreezy Calculating status...
Currently Being Moderated
Aug 4, 2012 8:16 PM

I've been through the F&F user guide and the numbers help guide. I'm either not seeing it or something is just going over my head. I know what I'm doing when it comes to my mac and normally I'd say I'm very capable with the standard functions/operations. I've been teaching myself the more "advanced" functions and so far it's been going well. But now I've hit a road block....

 

I'm trying to create a form for work, to calculate pricing for jobs.

The first table here is my reference table. the second is part of the billing sheet, which you'd be inputting information for specific jobs etc. in order to determine the price

 

They are on different sheets but within the same document. But I understand using formulas on different sheets, so I think for simplicity sake we can talk about them as if they are on the same sheet. (unless someone feels that's a no no). In case this is relevant, I used custom cell formats for 's/wrap' in table 2 (( ## 's )) and the prices listed in table 1 (( $##.##/m )).

 

 

 

4-page

6-page

8-page

Broad

Gate

10-Page

25's

$20.00/m

$22.50/m

$23.50/m

$26.50/m

$35.00/m

$25.50/m

50's

$15.00/m

$17.50/m

$18.50/m

$21.00/m

$30.00/m

$20.50/m

100's

$12.50/m

$15.00/m

$16.00/m

$18.50/m

$28.00/m

$18.00/m

None

$10.00/m

$12.50/m

$13.50/m

$17.00/m

$25.00/m

$16.50/m

 

 

 

Yes/No

TRUE

Perf / Score

FALSE

Fold Type

4-page

S/wrap

25's

$/m

 

Sub-Total

 

 

HERE'S MY PROBLEM:

 

I've been trying to figure out what function to use / how to express it so that the $/m box in the second chart will come up with the correct price as listed in the top table. I can't find anything about multi-dimensional searching except that it tells me "MATCH" can't be used multi-dimensional. I've used LOOKUP for some other basic functions but I don't really understand HLOOKUP nor VLOOKUP

 

In the explanation for LOOKUP it mentions something about "search-where" being two-dimensional, when I try to make it two dimensional it tells me vector arguments can't be, however the formula and function guide book doesn't mention the word vector once (at least that's what the search told me).

 

But I THINK (think being the key word here) what I need is for the "search-for" to be two-dimensional. I'd like to be able to input a fold type and the s/wrap quantity and BAM get my price listed below.

Is this possible?

 

Can someone help me out here? I've been at this for hours and I'm pulling my hair out. Am I not conceptualizing something correctly or arranging my data poorly?

 

Any and all help would be immensely appreciated.

I hope that wasn't too long winded.

 

To further complicate my life, there are price discounts for larger quantity jobs. I was trying to get around this by creating price tables for the different quantity ranges (i.e. 0-7500; 75001-15,000 etc.). That is what I have the YES/NO check box for at the top of table 2. I have four tables just like table 2 on the same sheet (each table is named differently so as to be clear). But if there is a way to simplify this, that would be awesome too. I won't get my hopes up, I think I'm asking for a Hail Mary as it is. I'll provide more information if someone thinks what I am talking about is a possibility.

iMac (27-inch Mid 2011), OS X Mountain Lion
  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Aug 4, 2012 8:53 PM (in response to gzcreezy)

    gz,

     

    I suggest that you take a page from the playbook of veteran programmers and start small and simple. Then add the bells and whistles.

     

    Get rid of every last custom format. Don't forget what you did, because you may go back to that, but you don't need that complication right now.

     

    Then, keep your tables separate, but put them on the same sheet for easy navigation and visual reference.

     

    If you don't find the array function that you think might make life easier, make do with an intermediate table. The extra step will make debugging easier. You can hide the extra table later. Almost anything you can do with array 2-D functions you can simulate with 1-D functions if you resort to interposing 1-D arrays.

     

    I'm at a loss to help you beyond these generalities because I don't find all the data you say you need to reference in your example.

     

    Jerry

  • Good Old Monkey Level 1 Level 1 (15 points)
    Currently Being Moderated
    Aug 5, 2012 6:08 AM (in response to gzcreezy)

    Hi, GZ,

     

    Thanks to Numbers' automatic creation of named ranges from header cell text, all you need is the INDIRECT() function. Assuming:

     

    • "4–page" as shown in your smaller table is in cell D6, and
    • "25's" is in cell D7

     

    in D8, to the right of "$/m", enter

     

    • =INDIRECT(D$6&" "&D$7)
    • obviously, maybe, you don't enter the •

     

    The terms in parentheses evaluate to "4–page 25's"; Numbers looks for a column headed "4–page" intersecting with a row headed "25's", and there you go. BUT NOTE THIS!

     

    Header text that can parse as a numeric expression like 4-page with a hyphen—which is also a minus sign—will confuse things. The simple solution is to use an en-dash. Hold down the <option> key while typing a hyphen, and you'll get the slightly longer en-dash: – instead of - .

     

    This has worked for me with your data; I hope it works for you, too.

     

    Cheers,

     

    David

  • Good Old Monkey Level 1 Level 1 (15 points)
    Currently Being Moderated
    Aug 5, 2012 6:11 AM (in response to Good Old Monkey)

    Huh! Looks like the en-dash was converted to a hyphen when my reply was posted on the system. But it's there in the character set, and you type it the way I said, and it will stop Numbers from confusing hyphens with minus signs.

     

    David

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Aug 5, 2012 8:53 AM (in response to Good Old Monkey)

    You may have found a good use for Header Names as References. I find it a nuisance and a source of confusion.

     

    Glad your problem is solved.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Aug 5, 2012 9:38 AM (in response to gzcreezy)

    gz,

     

    INDIRECT is a way to create a cell reference from a string, either a literal string or the result of a text function. INDIRECT is not a search function. For example, if you enter the simple expression: =C2, the compiler knows to convert C2 to an address token. If you write: ="C2", your cell will simply display C2, not the contents of cell C2.

     

    However, if you write: =INDIRECT("C2"), the cell you enter that expession in will display the same thing that is being displayed in cell C2 because INDIRECT converts the string "C2" to the address C2.

     

    Regards,

     

    Jerry

  • Good Old Monkey Level 1 Level 1 (15 points)
    Currently Being Moderated
    Aug 5, 2012 9:49 AM (in response to gzcreezy)

    GZ,

     

    INDIRECT() uses the table it's in, or if you want to reference another table, put it in like this:

     

    =INDIRECT("that_other_table::"&$C2&" "&$D2) or something similar. OR you could have the name of the other table plus its two colons in cell B2, and then you'd say =INDIRECT($B2&" "&$C2&" "&$D2) Don't forget

    • two colons :: after the table name
    • &" "& between references if you want to end up with a result Numbers can interpret as two ranges.

     

    Cheers,

     

    David

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Aug 5, 2012 10:05 AM (in response to gzcreezy)

    gzcreezy wrote:

     

    Maybe I just don't understand the linguistic scheme of the F&F guide. But I really don't find it helpful at all. I don't feel it breaks down the concepts simply enough. But I think I need to study it more instead of skimming and looking for information I think I need. I should go through it more thoroughly.

    I think you will find that the definitions in the Formulas and Functions user guide were essentially taken from Excel guides, where they were essentially taken from spreadsheet programs upon which Excel was written. It's a legacy thing. The best way to understand the workings of the functions is to study the examples.

     

    Edit: Regarding the multiple tables with the same name. It's good to try to avoid that. Yes, you can make them unambiguous by placing them in different Sheets, but that just complicates the addressing. Go ahead and put your unambiguously named tables in whatever sheets you want, but since they are not repeated names, you can leave off the sheet name when addressing the tables.

     

    Jerry

  • Good Old Monkey Level 1 Level 1 (15 points)
    Currently Being Moderated
    Aug 5, 2012 10:36 AM (in response to Jerrold Green1)

    Hi again, Jerry,

     

    If not with header names, which confuse me all the time, how do you create a three-variable lookup table? Let's say I want to find the price of Jumbo Frozen Shrimp in a situation where my seafood items come in Large Jumbo and Colossal sizes, Fresh or Frozen, and include Shrimp, Scallops, Scrod and Swordfish?

     

    Once I've remembered how to organize multiple headers, I find the formula = Jumbo Frozen Shrimp really easy to understand. It was actually because Google Docs lookup functions don't work right, and because I found that form of reference easy to understand once I had the right hierarchy of headers, that I'm doing most of my current model in Numbers. But if there's an easier way, I'd love to know it.

     

    Cheers,

     

    David

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.