LinkBack URL
About LinkBacks
Dear All,
Could you please help me in creating two way look up
Attacehd demo file is for to help me.
Thank you in advance.
Two Criteria look up
Dear All,
Could you please help me in creating two way look up
Attacehd demo file is for to help me.
Thank you in advance.
Click on (*), if you agree.
Have a look at the attached for one way of accomplishing this.
BSB.
Check the attached file..
I also suggest you to try ADVANCED FILTER TECHNIQUE if data is larger..
Use + Sign instead of * if you mean OR ..
Formula:
Please Login or Register to view this content.
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
can you pl do it by V lookup
What is wrong with either of the solutions provided?
If you want to use VLOOKUP then the easiest way would be to create a "helper column" that combines the two columns into one, then use that as your reference column in VLOOKUP as usual.
BSB.
You can do a vlookup - if you want.
1. Insert a new A column.
2. Join 'Team' and 'Person name' with this formula =B2&C2, then vlookup the joined column.
Alternative: Select the joined column: =B2&", "&C2
Hope it helped
Mark the problem as Solved under the Yellow bar up Top right, under Thread Tools, when you received a solution.
It saves time, to skip already solved threads.
Vlookup needs data in ascending order..
I am afraid that the results may very...
that why I used Index Match..
and Yes If you can rearrange your data in ascending order, that would be good for vlookup..
Vikas_Gautam
From Microsoft help page: "If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted."
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
I never use VLOOKUP, unless someone specifically requests it.
INDEX/MATCH is a better formula (safer, more flexible, etc).
Okay thanks for pointing out..
but what about second match..
As far as i know V look will also be a good options for this
=VLOOKUP(A2&B2,CHOOSE({1,2,3},Table1[Team]&Table1[Person Name],Table1[Age],Table1[Area]),2,0)
=VLOOKUP(A2&B2,CHOOSE({1,2,3},Table1[Team]&Table1[Person Name],Table1[Age],Table1[Area]),3,0)
try the above array formulas (Ctrl+Shift+Enter) in C2 and D2
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
For Age:
=VLOOKUP(A2,IF(B6:B20=B2,A6:D20),3,FALSE)
For Area:
=VLOOKUP(A2,IF(B6:B20=B2,A6:D20),4,FALSE)
Array Formula, means when ENTER you need to press CTRL-SHIFT-ENTER button together, not ENTER alone
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks