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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top