Forum Discussion

Brass Contributor

Jun 27, 2024

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

Document Tree.xlsx41 KB

excel

IlirU

Iron Contributor

Apr 24, 2026

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$166

Format these cells as you wish.

HTH

IlirU