vlookup

[crapit]

How do I use vlookup if ret =abcd, dept = 999 and return value of no = 4 ?

ret dept no
abdf 123 1
abcd 234 2
defg 999 3
abcd 999 4
rgrg 456 5
wdff 547 6
abcd 888 7

 

[Roger Govier]

Hi
One way
=SUMPRODUCT((A2:A8="abcd")*(B2:B8=999),C2:C8)

 

[Jason]

Another way is to add a 'helper' column in which you concatenate the two
fields on which you wish to match the criteria - in this case 'ret' and
'dept'.

Add a new column anywhere before the 'no' column and concatenate the two
fields, i.e. if 'ret' and 'dept' are in columns A and B, you could add a
new column (col C) and add the formula =A2&B2, then copy this down.

Then use the new concatenated field as the vlookup key, i.e. if you
wanted to lookup the ret =abcd, dept = 999 from your example:

=VLOOKUP("abcd"&"999",C210,2,false)

HTH

Jason

I'd personally go with the SUMPRODUCT and save on the need for helper
data. However, don't just use it and not understand how it works -
research how it works as it can be very powerful.