INDEX MATCH - strange scenario -

[SDM_44]

Hello all,

I am running an INDEX MATCH function (as I do frequently); honestly, this error has never happened to me - I have about 800 lines of data from the source tab in which I am trying to link over into a new tab (same workbook). For the vast majority of line items everything is fine - the data is coming over fine. But for certain fields in which I am linking over, I am getting a "null" or blank cell in return on the new tab (when there is definitely data in the source tab cell).

Seems very odd. Each line item has 12 fields. And again, for certain line items are returning all 12 values. Others are bringing back 10 or 11 values, with 1 or 2 blanks.

=IF(INDEX(FAL1_FV,MATCH($C67,FAL1_FV_AC_row1,0),MATCH(E$10,FAL1_FV_AC_clmn1,0))="","",INDEX(FAL1_FV,MATCH($C67,FAL1_FV_AC_row1,0),MATCH(E$10,FAL1_FV_AC_clmn1,0)))

C67 - is the link to the 2 tabs; FAL1....are the naming conventions to the source tab; E10 is the value on the top of each column in the source that the INDEX MATCH is looking up.

Let me know any thoughts. Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Can you post some data that shows the problems?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[SDM_44]

Hi & welcome to MrExcel.
Can you post some data that shows the problems?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks. The data is client-sensitive. I pasted below. As you see, line items 52, 53 are coming into the new tab fine; however, the Category for line item #54 is coming in blank, even though it has data in the respective source file line and is using the same formula as the two line entries above it.

52	AH	Location	Category	2019
53	AH	Location	Category	2019
54	AH	Location		2020

 

[Fluff]

There is virtually no way to debug the issue from what you have posted. We would need to see what is on the other sheet & where the formula is pointing.
Please post some de-sensitised data from both sheets using the XL2BB add-in.