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
\1
Last edited by iamhsn; 07-16-2012 at 03:18 PM.
You can use a nested IF:
or, if there is one unique match amongst the 3 columns:
Please Login or Register to view this content.
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.
Have you tried using Find instead of vlookup?
Perhaps..
=match(F3,A3:C3,0)
Life's a spreadsheet, Excel!
Say thanks, Click *
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
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.
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
Post an example excel spreadsheet, not a jpeg... and I will put in the formulas...
hope this VLOOKUP.xls works
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.2.Please Login or Register to view this content.in H4, update your formula to:Please Login or Register to view this content.
Please Login or Register to view this content.
duplicate post
didnt we cover all this here
http://www.excelforum.com/excel-gene...46#post2859546
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
@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
thanks to martindwilson. Your attachment worked. Cool
thanks to NBVC too. :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks