Hi, I am trying to retrieve data from a separate table by matching certain criteria in Excel 2010. I have used Vlookup but without success and now trying to use Index/Match but not completely getting the results. Please see attachment as it shows a better explanation and what the results should be.. Hope it makes sense ......thanks
d2try this and copy towards down
=INDEX($I$2:$I$10,MATCH(A2&B2,INDEX($G$2:$G$10&$H$2:$H$10,0),0))
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
or=LOOKUP(2,1/(($G$2:$G$10=A2)*($H$2:$H$10=B2)),$I$2:$I$10)
or use INDEX(MATCH(IF...))
=INDEX(H$2:I$10,MATCH(A2,IF(B2=H$2:H$10,G$2:G$10),0),2)
Array formula, use Ctrl-Shift-Enter
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Or
try
D2=OFFSET($G$1,MATCH(A2&B2,INDEX($G$1:$G$10&$H$1:$H$10,),0)-1,2,,)
Formula:
=OFFSET($G$1,MATCH(A2&B2,INDEX($G$1:$G$10&$H$1:$H$10,),0)-1,2,,)
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Or, another option: Array Formulacommitted with Ctrl-Shift-Enter rather than just Enter.Formula:=INDEX($I$2:$I$10,MATCH($A2&$B2,$G$2:$G$10&$H$2:$H$10,0))
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks to nflsales, Special-K, Shukla-Ankur281190 and TMS, All formulas work perfectly....Now I will go and practice them within my spreadsheet. Thanks once again.
You're welcome.
Just for reference, see the attached updated sample workbook. It has all the five formula for comparison.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks