+ Reply to Thread
Results 1 to 13 of 13

vlookup using multiple table_array

  1. #1
    Registered User
    Join Date
    05-07-2012
    Location
    Dhaka
    MS-Off Ver
    Excel 2016
    Posts
    70

    Angry vlookup using multiple table_array

    to match data from column A, B, or C this is what i'm using in F3: =VLOOKUP (F3,A3:D20,4,FALSE) but this formula looks only in A3 column but i want the formula to look into A3 column first if not matched then column B3 and if its still not matches then C3 column. hope someone can make it possible?

    Screenshot
    http://3.bp.blogspot.com/-Vl-kFQUgZ4...-o/s320/SS.JPG
    Last edited by iamhsn; 07-16-2012 at 03:18 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: vlookup using multiple table_array

    You can use a nested IF:

    Please Login or Register  to view this content.
    or, if there is one unique match amongst the 3 columns:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    10-07-2011
    Location
    Plano, TX USA
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: vlookup using multiple table_array

    Have you tried using Find instead of vlookup?

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: vlookup using multiple table_array

    Perhaps..

    =match(F3,A3:C3,0)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Registered User
    Join Date
    05-07-2012
    Location
    Dhaka
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: vlookup using multiple table_array

    the situation is whatever code i enter in F3 cell, it will first try to match at A3:A20 if not matched then further look into B3:B20 again if not matched then it will search in C3:C20 cells. When matched it will show the correspondent data(which is in D3:D20) into cell G3.
    Please take a look at the screenshot to understand it

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: vlookup using multiple table_array

    Have you tried my too offerings?

    If a match may potentially occur in any, some or all of columns A:C, then try my first..

    If a match will only occur once in those 3 columns, then try my second.

  7. #7
    Registered User
    Join Date
    05-07-2012
    Location
    Dhaka
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: vlookup using multiple table_array

    sorry but none of then worked. It needs to work what i enter in F3 cell but your method wont return any result based on what i enter in F3 cell

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: vlookup using multiple table_array

    Post an example excel spreadsheet, not a jpeg... and I will put in the formulas...

  9. #9
    Registered User
    Join Date
    05-07-2012
    Location
    Dhaka
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: vlookup using multiple table_array

    hope this VLOOKUP.xls works

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: vlookup using multiple table_array

    1. Your actual table starts at Row 4, so first search value is in F4, not F3
    2. For blanks, you can add IF() to check blank first
    3. Both formulas work with these changes

    1.
    Please Login or Register  to view this content.
    2.
    Please Login or Register  to view this content.
    in H4, update your formula to:

    Please Login or Register  to view this content.

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup using multiple table_array

    duplicate post
    didnt we cover all this here
    http://www.excelforum.com/excel-gene...46#post2859546
    Attached Files Attached Files
    Last edited by martindwilson; 07-17-2012 at 10:24 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  12. #12
    Registered User
    Join Date
    05-07-2012
    Location
    Dhaka
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: vlookup using multiple table_array

    @NBVC
    formulas you provided aren't working. You see when i enter a code (digits) in F4 cell, it will then show the associated description from "D" cells

    your formula dont depend on code in F cell

    here's a file with explanation....
    Thank you for your time
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-07-2012
    Location
    Dhaka
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: vlookup using multiple table_array

    thanks to martindwilson. Your attachment worked. Cool

    thanks to NBVC too. :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1