Forum Discussion
Cross Referencing Data in Excel
Hello,
I have a spreadsheet that lists document numbers that are stored in a document management system. I have to cross reference the documents in that management system, which means I have to go into each individual document in the management system and add in all the documents where that individual document is referenced. The attached spreadsheet lists all the documents in our system (column B) and each document where they are referenced (columns C-AP). Is there a way to have Excel search columns C-AP, find a specific value, and provide me with a list from column B where that value is found in columns C-AP?
9 Replies
A pithy solution to this task:
=FILTER(Documents,BYROW(ReferencedDocuments=DocInput,OR),"None")I like using named items because the formula reads well without explicit references.
alternatively
=TOCOL(IF(ReferencedDocuments=DocInput,Documents,NA()),3)which using sheet ranges in Oliver's sheet would be:
=TOCOL(IF(C2:AP162=C166,B2:B162,NA()),3)and BTW, I just saw your response and if you are getting a blank in col C and #CALC! in column E that is probably because the value you are searching for (C166) is not found. Make sure you have entered the text EXACTLY as it is in the search range.
as for "not an accurate list" what does that mean? please note that I see in column C the formula is only copied down 14 cells so that should be copied down more rows (but for the 365 solution that won't matter)
Hi Sue_G,
For me the formula given by Oliver Scheurich works very well. However, since you say you use Excel 365 then you can apply the formula below which is a dynamic formula (so no need to drag it down).
=INDEX(B2:B162, UNIQUE(TOCOL(SEQUENCE(ROWS(C2:AP162)) / SEARCH(C166, C2:AP162), 3)))I have manually set the reference that is required to be found in cell C166 and I have applied my formula in cell C168.
If you want to use Conditional Formatting then select cells C2:AP162 and apply this formula:
=C2:AP162 = $C$166Format these cells as you wish.
HTH
IlirU
=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.
