Lookup more than one number (Excel 2002)

Topic

New Reply

Hi

Ineed a vlookup formula to search on more than one number, if this is possible please.

Please see attached sheet.

Many thanks

Braddy

Reply | Quote

Replies

Hi Braddy

The attached spreadsheet shows a possible solution using the INDEX and MATCH functions in an array formula. To enter the formula you use Control+Shift+Enter

The 2 values to search for are concatenated and this value is then used to search through an array made up of concatenated values from the lookup table. Unfortunately it is not a particularly easy or elegant solution.

In the spreadsheet the values to lookup are in cells A2 & B2, the result in Cell C2 and the lookup table in cells E2:G8

Reply | Quote

I assume AddNum is the lookup range, and col 4 is the value to get. I will also assume that the item code is in column1 and the Cust code in COl 2 of the range.

If the item code and Customer are each unique in their respective lists, you could do a MATCH on each and if the rows were equal indx on the “value” list to get that row. This will not work if either is not unique

This formula could look like:
=IF(MATCH(A2,INDEX(AddNum,,1),0)=MATCH(B2,INDEX(AddNum,,2),0),INDEX(AddNum,MATCH(B2,INDEX(AddNum,,2),0),4),””)

If they are not unique and there is only 1 such combination this ARRAY formula should work (confirm with ctrl-shift-enter)
=SUM(IF((INDEX(AddNum,,1)=A2)*(INDEX(AddNum,,2)=B2),INDEX(AddNum,,4)))

If there are multiple combinations, the above will sum them up (you can replace the sum with other stat functions (count, average, min, max, std, etc) if desired.

If your needs are different than outlined, you will need to elaborate and probably create a custom function.
Steve

Reply | Quote

Hi Steve

The codes are not unique they repeat several time in the sheet , for instance the Item code will appear along side several different customer codes. hence the neeed to make sure they match .

So I will try the second formula the array ,

Thanks to you and Tony for your reply.

Braddy

Reply | Quote

You could do a multi-key lookup by extending your lookup table (addnum) with an additional column by concatenating its first two columns…

Let B2:E20 on Sheet2 house the lookup table with Item Code in B and Customer in C.

In A2 enter & copy down:

=B2&”,”&C2

Now select A2:E20 and name the selection “addnum”.

This setup allows you to invoke an ordinary VLOOKUP formula…

=VLOOKUP(A2&”,”&B2,addnum,5,0)

where A2 houses an Item Code interest and B2 a customer of interest on, say, Sheet1.

See attachment.

Reply | Quote