Using the results of ROW function as cell reference for another function

Question

just wondering if this is possible, I haven't seen an example of it.  I have data that I need to match from one excel workbook to another. I have no idea which row the matching data will be in, but I need to use the result of where the data is, as part of another formula.   So far, I've got the ROW part figured out, to be able to find the match in row 16 or 22 or whatever, but how do I blend that with the Column? I looked at INDIRECT and CONCATENATE, but if that's the answer, then I am missing something.
Is it possible to refer to a specific cell using the output of the ROW command?   like =IF(A22=3) but the 22 would come from the ROW function.   So =IF(A&ROW()=3)
Thanks

ExcelUser2021 · Answer

thanks for your help. I ended up solving the problem, using both INDEX and MATCH as per this link: https://exceljet.net/index-and-match. Wrapped the whole thing in an IF statement to verify if the dynamically found cell value matched an expected value and then displayed "Y" or "N" depending on the result.

Answered by ExcelUser2021 on January 23, 2021

Ron Rosenfeld · Answer

I suggest using the result of the ROW function as an argument to the INDEX function.  Unlike INDIRECT, INDEX is non-volatile, and will work on closed workbooks.
eg: To return the contents of C42 from some other workbook, where 42 is in A1 where you have the formula that determined the row number:
=INDEX('Path[fileName]worksheetName'!$C:$C,A1)

Path is required if you write the formula when the "other" workbook is closed; not so if you write the formula when the other workbook is open
Or, you could replace A1 with the formula whose result is the row number you hope to use.

Using the results of ROW function as cell reference for another function

2 Answers

Add your own answers!

Ask a Question