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)

 

[spyldbrat]

I am no longer getting an error, but it's displaying the incorrect data for the value look up. Below are two examples:

Look up value on spreadsheet with formula: 21277 NJ EE Stmt Rcvd in Corp
Displaying the Value Lookup for "12961 NJ SM Form/Rcpt Rcvd in Corp" from the source spreadsheet

Look up value on spreadsheet with formula: 21508 NJ EE Stmt Rcvd in Corp
Displaying the Value Lookup for "12961 NJ Temp Cert Rcd in Corp" from the source spreadsheet

Below is the formula I have entered. My data does not start until row 8 and does not go beyond 500.

=INDEX('[Compliance Response.xlsm]NJN'!$H8:$H500,MATCH(L8,'[Compliance Response.xlsm]NJN'!$L8:$L500,0))

 

[Aladin Akyurek]

I am no longer getting an error, but it's displaying the incorrect data for the value look up. Below are two examples:

Look up value on spreadsheet with formula: 21277 NJ EE Stmt Rcvd in Corp
Displaying the Value Lookup for "12961 NJ SM Form/Rcpt Rcvd in Corp" from the source spreadsheet

Look up value on spreadsheet with formula: 21508 NJ EE Stmt Rcvd in Corp
Displaying the Value Lookup for "12961 NJ Temp Cert Rcd in Corp" from the source spreadsheet

Below is the formula I have entered. My data does not start until row 8 and does not go beyond 500.

=INDEX('[Compliance Response.xlsm]NJN'!$H8:$H500,MATCH(L8,'[Compliance Response.xlsm]NJN'!$L8:$L500,0))

Probably because the ranges are not abosute. Try...

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

 

[Scott T]

If you are copying the formula down then you want the H8:H500 to be absolute references so that it always looks at the same range.

=INDEX('[Compliance Response.xlsm]NJN'!$H[B]$[/B]8:$H[B]$[/B]500,MATCH(L8,'[Compliance Response.xlsm]NJN'!$L8:$L500,0))

 

[spyldbrat]

I tried both of your formulas and it's still not working. The data that was displaying came from rows 13 and 14 from the source spreadsheet. After I made the cells absolute, the data that is displaying is coming from rows 8 and 9 of my source data. This happened with both of your suggestions.

=INDEX('[Compliance Response.xlsm]NJN'!$h$8:$h$500,MATCH(L8,'[Compliance Response.xlsm]NJN'!$L8:$L500,0))

 

[Aladin Akyurek]

I tried both of your formulas and it's still not working. The data that was displaying came from rows 13 and 14 from the source spreadsheet. After I made the cells absolute, the data that is displaying is coming from rows 8 and 9 of my source data. This happened with both of your suggestions.

=INDEX('[Compliance Response.xlsm]NJN'!$h$8:$h$500,MATCH(L8,'[Compliance Response.xlsm]NJN'!$L8:$L500,0))

You need:

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

​

 

[Scott T]

You have the same problem with your L8:L500 range it should also be absolute.

=INDEX('[Compliance Response.xlsm]NJN'!$H[B]$[/B]8:$H[B]$[/B]500,MATCH(L8,'[Compliance Response.xlsm]NJN'!$L[B]$[/B]8:$L[B]$[/B]500,0))

 

[spyldbrat]

I tried that too. Same result.

 

[Aladin Akyurek]

I tried that too. Same result.

=INDEX('[Compliance Response.xlsm]NJN'!$H$8:$H$500,MATCH($L8,'[Compliance Response.xlsm]NJN'!$L$8:$L$500,0)) <!-- edit note -->

​

means:

compare L8 with $L$8:$L$500
if L8 is in $L$8:$L$500, give its position to INDEX which picks out a value from the same position in $H$8:$H$500.

If this is indeed what you want, care to post what you have in L8, not L8 of $L$8:$L$500?

 

[spyldbrat]

Oh my gosh - I just realized what I did. I was testing data that didn't actual start until line 162 of my spreadsheet. I was putting L8 instead of L162 into this cell. I am so sorry for all the confusion but I do truly appreciate everyone's help and patience!!!!! Thank you all!!!!!

 

[Aladin Akyurek]

Oh my gosh - I just realized what I did. I was testing data that didn't actual start until line 162 of my spreadsheet. I was putting L8 instead of L162 into this cell. I am so sorry for all the confusion but I do truly appreciate everyone's help and patience!!!!! Thank you all!!!!!

You are welcome.