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
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