Lookup Using Vlookup and IF (Excel 2003)

Topic

New Reply

Hi

Title Prod1 Prod2 Prod3
Rep $1 $2 $3
SRep $4 $5 $6
Mgr $7 $8 $9

If SRep sells Prod3 I want to be able to cross reference and return $6.

I can use Index…Match…Match, and Vlookup… Match on this. How can I use VLookup with IF on this

TIA

regards, francis

Reply | Quote

Replies

Why would you want to use IF here? INDEX and MATCH is the best way to do this.

Reply | Quote

Hi Hans

I was able to get the answer with the other 2 formulas and a third using Sumproduct.
I am wondering whether a IF statement with Vlookup can be constructed, I have tried doing this but is unsuccessful
It is ok if there no solution using IF with Vlookup.

TIA

cheers, francis

Reply | Quote

It’s not clear to me what you would want to use IF for…

Reply | Quote

The attached uses both Vlookup and IF to find a solution.

But, I fully agree with Hans, using Vlookup combined with IF is not the best solution.

Tom Duthie

Reply | Quote

Your formula should be

=VLOOKUP(C19,MyValues,IF(C18=D10,2,IF(C18=E10,3,IF(C18=F10,4))),FALSE)

for you want to look for an exact match. If columns are added to the lookup table, the formula becomes unwieldy, and you will eventually run into Excel’s limit for nested functions.

Reply | Quote

Hi Hans

Thanks for the guide. Like I have mentoned, I wanted ti know if this can be done, although may not be the best solution.
Happy New Year to you and family.

cheers, francis

Reply | Quote

Hi Tom

thanks.
Happy New Year!

cheers, francis

Reply | Quote