Please help, conditional vlookup multiple pricing tables

Hi Tim,

1.

I see a mixture of Cell References. Some are Header Names and some are Column/Row.

To make it easier to trouble-shoot the formula, I suggest you go to Menu > Numbers > Preferences > General > Cell References and untick Use Header Names as References. Cell References will then become (for example) B3 instead of "Product 100".

2.

The IF function follows this logic:

If, then, else 😉.

If this is true, then do this, else do that

You have "=" instead of "," (representing"else") in front of the second and third ifs.

3.

When composing a formula, I find it easier to point and click on cells rather than typing cell references.

4.

Watch out for upper and lower case (best/Best). Pop-Up Menus in Column B of the first table will give exact entries and avoid typing errors.

Good luck and please call back.

Regards,

Ian.

Hi Tim,

If you put all combinations of area and quality (Good, Better, Best) into a single table, it will make it easier to use VLOOKUP. No need for a complex, nested IF formula.

Here is a subset of a Product Price List.

Formula in C2 (and Fill Down)

=B2&A2

The concatenate (&) operator joins B and A and gives a unique code for each product.

Now to look up a cost:

In the Find a Cost Table, enter Area and Quality.

Column C uses the same concatenate formula to create a single value for VLOOKUP to use.

D is the lookup function

Formula in D2 (and Fill Down

=IF(A2>0,VLOOKUP(C2,Product Price List::C:D,2,close-match),"")

If a cell in A has a value >0 (is not blank), then apply the LOOKUP formula to find a cost that matches, else insert "" (NULL, Blank) into D.

From the Function Help,

close match (TRUE, 1, or omitted): If there’s no exact match, select the row with the largest left-column value that is less than or equal to the search value.

I think that 'close match' is what you want, as the costs jump in steps in your example (for example, 72 inches is dearer than 1 to 71 inches).

You can hide the 'Product Price List' by cutting and pasting to another sheet. The formulas will automatically adjust.

You can hide column C of the 'Find a Cost' table.

Regards,

Ian.

G'Day Ian, you ole Dirt Doctor

can you have a look at this one - where the OP may need to get Numbers to share data with Keynote?
How to programmatically generate Keynote file

see you... ÇÇÇ

Hello

You may use INDIRECT() to build target table reference. Like this.

Table 1 A1 Area A2 64 A3 100 A4 91 A5 46 B1 Product B2 Best B3 Better B4 Good B5 Good C1 Cost C2 =VLOOKUP(A2,INDIRECT(B2&" Cost Table::A:B"),2) C3 =VLOOKUP(A3,INDIRECT(B3&" Cost Table::A:B"),2) C4 =VLOOKUP(A4,INDIRECT(B4&" Cost Table::A:B"),2) C5 =VLOOKUP(A5,INDIRECT(B5&" Cost Table::A:B"),2)

Formula in C2 can be filled down.

Tables are built with Numbers v2.

Good luck,

H

Or if you prefer, you may use nested IFs as follows.

Table 1 A1 Area A2 64 A3 100 A4 91 A5 46 B1 Product B2 Best B3 Better B4 Good B5 Good C1 Cost C2 =IF(B2="Best",VLOOKUP(A2,'Best Cost Table'::A:B,2), IF(B2="Better",VLOOKUP(A2,'Better Cost Table'::A:B,2), IF(B2="Good",VLOOKUP(A2,'Good Cost Table'::A:B,2)))) C3 =IF(B3="Best",VLOOKUP(A3,'Best Cost Table'::A:B,2), IF(B3="Better",VLOOKUP(A3,'Better Cost Table'::A:B,2), IF(B3="Good",VLOOKUP(A3,'Good Cost Table'::A:B,2)))) C4 =IF(B4="Best",VLOOKUP(A4,'Best Cost Table'::A:B,2), IF(B4="Better",VLOOKUP(A4,'Better Cost Table'::A:B,2), IF(B4="Good",VLOOKUP(A4,'Good Cost Table'::A:B,2)))) C5 =IF(B5="Best",VLOOKUP(A5,'Best Cost Table'::A:B,2), IF(B5="Better",VLOOKUP(A5,'Better Cost Table'::A:B,2), IF(B5="Good",VLOOKUP(A5,'Good Cost Table'::A:B,2))))

Notes.

Formula in C2 can be filled down.

Indents and newlines in formula are for readability and optional.

Regards,

H