Forum Discussion
Solved
Lookup for a value in an array
Hello, I have a use case where I have to look up for a value that can be in an array and output a value from the corresponding output array. Here is my input table. I have a Server Name column whi...
=IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$B$1:$B$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$B$1:$B$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$C$1:$C$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$C$1:$C$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$D$1:$D$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$D$1:$D$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$E$1:$E$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$E$1:$E$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$F$1:$F$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$F$1:$F$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$G$1:$G$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$G$1:$G$40,0))))))))
Is this what you are looking for? There is probably an easier way to achieve this but this is my best solution at the moment.
- Detlef_Lewin
This worked like charm and also addresses my concerns of adding more columns in future.
Thank you very much.
- @Qadruple_Pawn
Thank you very much for the reply.
This definitely works, I was hoping to avoid multiple queries, one for each column as I might to have more columns in future. I'm not sure if there is a easier way (xlookup, index/match combo or others?) that can be used.
