Index Match Array inconsistent data

Elle_H

New Member
Joined
Sep 29, 2015
Messages
12
Hello
I am and have been a huge fan of Index Match formula but never use it in an array to have multiple lookup values, until today. The formula is inconsistently working.

I need the formula entered in 1300 rows. When I enter the formula in the first cell, it works. When I copy down, it works in some cells and doesn't in others. What is interesting to me is that the cells where it is not working, have the same lookup values as the cells where it did work. What am I missing? I even go into a cell where it did not work and perform the CSE and it still returns #N/A.



Array Formula: =INDEX([Spreadsheet Name]TABNAME!$D:$D,MATCH(1,([Spreadsheet Name]TABNAME!$C:$C=AF11)*([Spreadsheet Name]TABNAME!$E:$E=AH11),0)) and then CSE

AF11 - is the start of the rows of data
"SPREADSHEET NAME" = I have a spreadsheet with 6 columns - Column C = a 3-letter code; Column D = The description of that code; Column E = a 1-letter code; Column F = The Description of that code


The spreadsheet with the formula has the codes and I need to bring over the descriptions.
A 3-letter code will be listed more than once with a different 1-letter code and descriptions will change based on the combinations, which is why I needed multiple lookup values.

When I copy down - formula works until row 52. Rows 52 - 837 = #N/A ; Rows 838 - 865 = displays data ; Rows 866 - 1310 = #N/A

Rows 51 and 52 have the same values in AF and AH. Why do you think the formula is inconsistent? What am I missing?


Thank you!!!
-Elle_H
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Rows 51 and 52 have the same values in AF and AH. Why do you think the formula is inconsistent? What am I missing?
Check if really AF51=AF52 and if AH51=AH52.
Try in empty cells
=AF51=AF52
=AH51=AH52

Both should return TRUE, otherwise i suspect there are extraneous characters (spaces?) in your data.

M.
 

Elle_H

New Member
Joined
Sep 29, 2015
Messages
12
Also, when I free type the formula (and CSE) in a cell where it did not pull data after copy down (#N/A), it still brings back #N/A. But I have other rows with the same exact values in AF and AH, where the formula did work.
 

Elle_H

New Member
Joined
Sep 29, 2015
Messages
12
Well I'll be. I don't even know how spaces could have gotten here. wtf. wow. That worked. M - you are AMAZING. Thank you thank you thank you!!!!! -Elle_H
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,435
Messages
5,486,869
Members
407,567
Latest member
spinitback

This Week's Hot Topics

Top