Forum Discussion
Cross Referencing Data in Excel
=IFERROR(INDEX($B$2:$B$162,SMALL(IF($C$2:$AP$162=$C$166,ROW($2:$162)-1),ROW(A1))),"")
This formula returns the expected results in my sample file in my understanding. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021. I've added conditional formatting which highlights the cells in range C2:AP162 where search string from cell C166 is found.
Hello,
As you can see, several years ago, you helped me with a spreadsheet and I've been using it ever since. However, the formula quit working properly, and I've not changed anything. It is supposed to search for the document ID in column B in columns C-AP and provide a list of all documents (from column B) where the documents are found. It's not pulling the accurate information. Would you please review the file and let me know why it's no longer working? I use Excel 365. Thank you.
Hello again,
I'm sorry, I should have provided more information. I re-downloaded the original file you provided as mine wasn't working accurately. The original file doesn't work accurately either. I'd be happy to provide you the file I actually use, but am unable to attach to this reply. Thank you.
Hi,
the old formula still returns the expected results in my sample file. In the attached file i've added a formula for Excel 365 that spills the result.
HI Oliver,
I don't understand what I'm doing wrong. This is odd as I've been using this file since you first created it for me and it's worked as expected. When I enter in a new number (either typing it directly or copy and paste), it does not give me an accurate list, even in the updated file for Excel 365 you attached, and in some cases, I get nothing listed in column C and a #CALC! error in column E under Documents returned by formula. Is it possible that I have a setting in Excel that would prevent this formula from working? Can you suggest what I might look at to see if this is the problem?