Using Index Vs Vlookup

[bzl]

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

[samba_ravi]

d2

Please Login or Register  to view this content.

try this and copy towards down

[samba_ravi]

or

Please Login or Register  to view this content.

[Special-K]

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

[shukla.ankur281190]

Or

try

D2=OFFSET($G$1,MATCH(A2&B2,INDEX($G$1:$G$10&$H$1:$H$10,),0)-1,2,,)

Please Login or Register  to view this content.

[TMS]

Or, another option: Array Formula

Please Login or Register  to view this content.

committed with Ctrl-Shift-Enter rather than just Enter.


Regards, TMS

[bzl]

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.

[TMS]

You're welcome.

Just for reference, see the attached updated sample workbook. It has all the five formula for comparison.

Regards, TMS