extracting a corresponding value from col L if columns B-K match in 2 sheets

[dianeblack]

I have 2 tabs on my spreadsheet, with columns A-K containing the same data columns, although contents may vary.

I need to enter the Reference from column L on sheet b into the corresponding column L on sheet a if the data in columns B to K matches.

I have had a go at the formula based on other threads here, but it doesn't seem to produce a result. It may be because I am not entering it as an array correctly and so cant get the wobbly brackets without typing them in (which still doesn't seem to work anyway!)

Help!

[jeffr27]

Try this on 'sheet a' cell L1: =IF(SUMPRODUCT((A1:K1='Sheet b'!A1:K1)*1)=11,'Sheet b'!L1,FALSE) and drag down.

[dianeblack]

That has done the trick. Perfect. Thank you so much

[dianeblack]

Sorry, Jeffr27
I have just added some extra data to this sample and there is one thing that doesn't quite work:
I should have said that the row contents do not exactly match between the spreadsheets as there is different data on each sheet, but SOME rows match. Is there a way to change the formula slightly to reflect this? Example attached. I am looking for a match on the whole sheet rather than just the corresponding row. Hope this makes sense?

[jeffr27]

Diane, I added a column to concatenate columns A-K for both sheets and then compared the results (see attached). I'm sure there's a way to do it without adding a new column, but it's not coming to me yet. Let me know if this works.

Oh, and on your original post, you can't enter array formulas by manually typing in the curly brackets. You have to press Ctrl+Shift+Enter instead of just Enter when typing your formula.

[jeffr27]

I figured it out without adding the concatenate column. Put this rather lengthy formula in L1: =IFERROR(INDEX('Sheet b'!$L$1:$L$5,SUMPRODUCT((A1='Sheet b'!$A$1:$A$5)*(B1='Sheet b'!$B$1:$B$5)*(C1='Sheet b'!$C$1:$C$5)*(D1='Sheet b'!$D$1:$D$5)*(E1='Sheet b'!$E$1:$E$5)*(F1='Sheet b'!$F$1:$F$5)*(G1='Sheet b'!$G$1:$G$5)*(H1='Sheet b'!$H$1:$H$5)*(I1='Sheet b'!$I$1:$I$5)*(J1='Sheet b'!$J$1:$J$5)*(K1='Sheet b'!$K$1:$K$5)*ROW('Sheet b'!$A$1:$A$5))),FALSE)

This formula checks each column for a match and returns the row number if a row match is found. However, this formula requires there are no duplicate rows on sheet b (ex. row 2 and row 4 cannot be the same because the formula would return row 2 + row 4 = row 6).