Forum Discussion
This should be a simple problem
so CONCAT will create a TEXT value of A11 and that is different than a cell reference.
As noted by Detlef you can use =INDEX(A1:A100, MATCH( .... ) )
You can (and should consider) use XLOOKUP( ). This is a newer function (need 2019 or 365 to use) and is more versatile and more efficient. =XLOOKUP( value, lookup_array, A1:Axxx, "not found") I put Axxx in the range because the return_array and lookup_array must be the same length.
so CONCAT will create a TEXT value of A11 and that is different than a cell reference.
As noted by Detlef you can use =INDEX(A1:A100, MATCH( .... ) )
You can (and should consider) use XLOOKUP( ). This is a newer function (need 2019 or 365 to use) and is more versatile and more efficient. =XLOOKUP( value, lookup_array, A1:Axxx, "not found") I put Axxx in the range because the return_array and lookup_array must be the same length.
Thanks for your time. I tried the INDEX function, but I ran across the function XLOOKUP and I ultimately used it as follows;
=XLOOKUP($A116,'By Name'!$D$2:$D$133,'By Name'!$A$2:$A$133," ")
A116 is the value in the sheet I'm looking for in the "By Name" sheet in Column D 2 though D 133.
If the value is found the cell with the formula (B116) will get the ROW result of the column "By Name" Column A {ROW].
If the value was not found searching column D, then B116 gets a space character.
I used the $ for all columns and all but 1 row, the A row. This was so when I did numerus copies of this formula I didn't want the paste to change the column or row.