Index & Match Help

[spyldbrat]

I have two spreadsheets: 2017-08-03 Compliance Response and Current Compliance. Both spreadsheets have the same column and column headers.

2017-08-03 COMPLIANCE RESPONSE/NJN Tab - I need to add the data from column K using column L as my lookup value to column H of the "CURRENT COMPLIANCE". I cannot move my lookup value column on the spreadsheets.

I tried and Index/Match formula but it's not working. This is the formula I have in column H of CURRENT COMPLIANCE. What is wrong with my formula?

=INDEX($A$8:$M553,MATCH(L8,'Compliance Response.xlsm]NJN'!$A8:$M500,0),12)

 

[Aladin Akyurek]

Perhaps just...

=INDEX($A$8:$M553,MATCH(L8,'[Compliance Response.xlsm]NJN'!$A8:$M500,0),12)

 

[Scott T]

The lookup array of match should only have a column OR row, A8:A553 or A8:M8. This is how match knows if it is returning a row number or a column number.

With out seeing your data this is a guess but try

=INDEX($K$8:$K553,MATCH(L8,'[Compliance Response.xlsm]NJN'!$H8:$H553,0))

 

[Aladin Akyurek]

Indeed there is more that is wrong...

=INDEX('[Compliance Response.xlsm]NJN'!$A$8:$M553,MATCH(L8,'[Compliance Response.xlsm]NJN'!$A8:$A553,0),12)

which matches L8 against the A range and returns a value from the L range (12th column in A8:M553).

 

[spyldbrat]

I tried all the examples above...none of them seem to work

 

[Aladin Akyurek]

I tried all the examples above...none of them seem to work

Then try to explain one by one:

What is the name of the external workbook you refer to?

Which sheet contains the data?

What is the look up value?

Against which range must the look up must be matched?

From which range must a result be returned if the matching succeeds?

 

[spyldbrat]

hen try to explain one by one:

What is the name of the external workbook you refer to?
2017-08-03 COMPLIANCE RESPONSE

Which sheet contains the data?
NJN

What is the look up value?
Column L

Against which range must the look up must be matched?
I am not sure what you are asking?

From which range must a result be returned if the matching succeeds?
Column H

 

[Aladin Akyurek]

hen try to explain one by one:

What is the name of the external workbook you refer to?
2017-08-03 COMPLIANCE RESPONSE

Which sheet contains the data?
NJN

What is the look up value?
Column L

Against which range must the look up must be matched?
I am not sure what you are asking?

From which range must a result be returned if the matching succeeds?
Column H

Where/in which column must L8 (from column L), which is the look up value, be searched in order to get a result from H?

 

[spyldbrat]

I am still not understanding what you are exactly asking but column L is has the common data (Value Look Up) between the two spreadsheets. I need it to match column L between the two spreadsheets and return the value in column H from my source spreadsheet. My data starts on row 8 on both spreadsheets. I hope this makes it more clear.

 

[Aladin Akyurek]

Try...

=INDEX('[Compliance Response.xlsm]NJN'!$H$3:$H$553,MATCH(L8,'[Compliance Response.xlsm]NJN'!$L$3:$L$553,0))