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

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

--
Regards
Roger Govier

crapit wrote:
> 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
>
>
>
>

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.






Roger Govier wrote:
> Hi
> One way
> =SUMPRODUCT((A2:A8="abcd")*(B2:B8=999),C2:C8)
>
> --
> Regards
> Roger Govier
>
> crapit wrote:
>> 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
>>
>>
>>
>>