Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    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
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Using Vlookup and IF (Excel 2003)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Using Vlookup and IF (Excel 2003)

    Hi Tom

    thanks.
    Happy New Year!

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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