Forum Discussion
This should be a simple problem
I've used the MATCH function to find the row in the column that matched what I'm looking for. I now want to see the value in a different column in that row. I can't find a way to build a cell reference.
In my test spreadsheet, MATCH has given me row 11 and now I want to see what is in column A (A11).
I've tried CONCAT, VALUE, ADDRESS and other functions, but the all seem to give ERROR or VALUE failure or just don't work. If I hand build a cell with =A11 it works, but if I use CONCAT to build =A11 it doesn't work.
I've been trying a variety of combinations for about 6 hours. I hope someone is interested in my problem and can give some suggestions I haven't tried or to tell me it can't be done.
Thanks in advance,
Clark
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.
3 Replies
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.
Try INDEX().
