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 ...
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)
