# Thread: Lookup Using Vlookup and IF (Excel 2003)

1. ## Lookup Using Vlookup and IF (Excel 2003)

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

2. ## Re: Lookup Using Vlookup and IF (Excel 2003)

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

3. ## Re: Lookup Using Vlookup and IF (Excel 2003)

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

4. ## Re: Lookup Using Vlookup and IF (Excel 2003)

It's not clear to me what you would want to use IF for... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

5. ## Re: Lookup Using Vlookup and IF (Excel 2003)

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

6. ## Re: Lookup Using Vlookup and IF (Excel 2003)

=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.

7. ## Re: Lookup Using Vlookup and IF (Excel 2003)

Hi Tom

thanks.
Happy New Year!

cheers, francis

8. ## Re: Lookup Using Vlookup and IF (Excel 2003)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•