VLOOKUP function based on two inputs? help.

Question

ACR1982 Author

Level 1

0 points

VLOOKUP function based on two inputs? help.

Apparently VLOOKUP will not search-for two values, is this correct?

Therefore i most probably need an alternative, all help will be appreciated.

My problem;

i'm trying to make an auto fill template for some engineers in iPad Numbers.

3 columns (A, B, C), the first two (A, B) are user defined from a set pop-up menu they are text, i want the third column (C) to to autofill, depending upon the first two options selected in columns A & B - all on the same row.

Data sets (pop-up Menus) are finite, the first column range is 1 of 15 values, the second column is 1 of 13 values, and the third column is unique according to what combination of values in columns A & B, all this data is on another separate sheet complete.

How would i go about doing this? The only thing i can think of is 'IF' each different option in cloumn A, then separate 'VLOOKUP' small selection in column B, but then the formula would be huge!!!

Any suggestions or pointers please?

Regards

iPad, iOS 5.1

Posted on May 22, 2012 9:22 AM

Reply

Answer 1

Best reply

ACR1982 Author

Level 1

0 points

May 22, 2012 9:36 AM in response to ACR1982

I am silly, just made a combined column of values and used & in search for.

Reply

Answer 2

Barry

Level 9

59,861 points

May 22, 2012 3:50 PM in response to ACR1982

Hi AC,

VLOOKUP can't 'look for two values' by itself, but you could use MATCH to select the column from which VLOOKUP will return a value. Here's an example using a seven by seven matrix for the Lookup table.

Data in the lookup table has been constructed to show the location from which it comes.

The main table on the left contains pop-up menus in columns A and B. Note the 'empty' entry at the top of the list in the open menu in column B. This first entry is actually a single space, which is as close to 'empty' as may be specified in a pop-up menu defined by the user.

Menu items in column A match the labels in column A of the lookup table.

Menu items in column B match the labels in Row 1 of the lookup table.

Column C contains the formula below:

=VLOOKUP(A,Lookup :: $A:$H,MATCH(B,Lookup :: $1:$1,0),FALSE)

Because both VLOOKUP and MATCH specify an 'exact match' ("FALSE" in VLOOKUP, "0" in the enclosed MATCH), the formula will throw an error if a match is not made for either of the menu settings. The basic formula above is enclosed in an IFERROR statement to trap this error and return the text string "--" in place of the error message (as in lines 2 and 10 of the Main table):

=IFERROR(VLOOKUP(A,Lookup :: $A:$H,MATCH(B,Lookup :: $1:$1,0),FALSE),"--")

Regards,

Barry

Reply