help with a vlookup and index

[d7]

I am trying to pull a value through i have in sheet 1:-

ab1 october 100
ab1 november 101
az1 november 151
az1 december 152

And in sheet 2 i have:-

ab1 november (need to Return the matching Value) (which is 101)
az1 december (as above) (but 152)

 

[Max]

One way

Assume source data as posted in Sheet1's A2:C5

In Sheet2,
you've got these listed in A2:B2 down
ab1 november
az1 december
etc

Place in C2, normal ENTER
=INDEX(Sheet1!$C$2:$C$5,MATCH(1,INDEX((Sheet1!$A$2:$A$5=A2)*(Sheet1!$B$2:$B$5=B2),),0))
Copy C2 down to return required results. Adapt the ranges to suit. This
index/match expression is generic, ie it will work even if Sheet1's col C
contains text/mixed data.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik

 

[muddan madhu]

another one : -

=INDEX(sheet1!C2:C10,MATCH(sheet2!A2&sheet2!B2,sheet1!A2:A10&sheet1!
B2:B10,0),0)

This is an array function use ctrl + shift + enter