Double VLOOKUP

  • Thread starter Thread starter papegoja
  • Start date Start date
P

papegoja

Hi,
I have a list with tre columns.
Ordernumber, Materialnumber, Amount.

Ex:
23001, MAT01, 14
23001, MAT12, 12
23001, MAT03, 22
23002, MAT11, 49
23002, MAT01, 24
23002, MAT03, 14
23003, MAT01, 47
23004, MAT01, 12
23004, MAT03, 14

Then I have alist that contains the ordernumbers and material.
I would want to get the amount of the material for that exact orde
into a cell.

Ex:
If i have a row with Ordernumber 23002 and Material MAT01, I want it t
return 24.
If I use a normal VLOOKUP it would return the first hit = 49.
But I want the formula to first search for hits in the first column an
then search within these hits for a new value.

Can it be done?

Best regards
/Dan
 
Hi
some ways:
1. Use the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100=23001)*
(B1:B100="MAT12"),0))

of course you can replace the hardcoded conditions with
cell references.

2. If column c contains only numbers you may use the non
array formula:
=SUMPRODUCT(--(A1:A100=23001),--(B1:B100="MAT12"),C1:C100)
 
Let's say the table is in A1:C9 of Sheet2
On Sheet2 insert a column before A
In the new A1 cell enter =B1&C1 and copy down to A9

On Sheet1 were we want to do a lookup, in A1, B1,C1 we have labels: Order,
Material Amount
In A2 enter 23002, in B2 enter MAT01, in C2 enter
=VLOOKUP(A2&B2,Sheet2!A1:D9,4,FALSE)

This correctly returns 24

best wishes
 
Back
Top