Solved: Match two criteria and display offsets? worksheet function

[Simon Lloyd]

Hi all, is it possible to look at two different cells, match them to celss in another sheet and then display the offset?

If A2 contained "ABC 123" and D2 contained "M" then find both of these in the same row on sheet2 (regardless of the letter contained in D1 they always appear in the same row, i.e A65 ="ABC 890" D65 ="M" the match for these will appear together on a row in sheet 2 so they both might appear on say row100, once found on sheet 2 display the offset from the "found" M (0,-5). Formula to go in G2

I have tried Vlookup and match but could not get it to equate to anything sensible.

[Bob Phillips]

Will the A123 etc always be in column A, and the M always in column D? If so, how can you offset -5 columns?

[Simon Lloyd]

Sorry for misleading you Bob, what i omitted to say the values are on sheet1 and if they appear in sheet1 then a formula in G2 of sheet1 (i will copy the formula down as needed) should look for a match of both A2 & D2 in sheet2 (they would not match across different rows, the dual occurrence is always in the same row) and then return the value offset(0,-5) from the D2 value found on sheet2.

SO if the value of A2 & D2 sheet1 occur in any row together (not seperate rows) sheet2, then if D2 value is found in column L on sheet2 then display the contents of G2 sheet2

Is that still a murky description?

if it is i will post a sample coulpe of worksheets

[Simon Lloyd]

Test sheet added

[Bob Phillips]

=INDEX(Sheet2!$1:$12,MATCH(1,(Sheet2!$A$1:$A$12=A1)*(Sheet2!$L$1:$L$12=D1), 0),7)

as an array formula

[Simon Lloyd]

Thanks Bob, i had a little trouble getting it to work with named ranges but got it sorted!

I did try an INDEX MATCH but its that * operator i didnt use and didnt know how to nest them! i also tried Vlookup INDIRECT but it would only return a value to the rght of course!