CODEWITHSUNDEEP
excelexcel-formula
Scrat
Scrat

Reputation: 33

How to search all worksheets and return cell in the same column as the found item?

I want to search multiple worksheets and return the value of the 2nd row in the column where the keyword was found.

I have the task of comparing a BOM list (basically a list of devices that will be installed in a data center) with the rack layout which is another excel file and where each rack looks like the attached image. Each device has a unique name that is used across all excel sheets. On every worksheet in the rack layout there are multiple racks.

Now I need a way to search through all the racks, find device I'm looking for and return the Rack Location which is always written in the 2nd row of the same column the device name is in.

E.g.: When looking for "B.DEF 04" I want the return value to be "Location 2"

I have already looked at VLOOKUP and WLOOKUP but they only look through the first column/row of the matrix. I have also tried INDEX and MATCH but could not get it to work since the value I'm looking for is not in a single row or column, which makes it hard to use MATCH.

enter image description here

Upvotes: 0

Views: 227

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

Found a solution here

You can use INDEX() with SUMPRODUCT().

Assuming you want to find B.DEF 02, put that in a cell (in my example, it's in T3) and run this array formula:

=INDEX($C$2:$Q$2,SUMPRODUCT(MAX(($C$3:$Q$5=T3)*(COLUMN($C$3:$Q$5))))-COLUMN($C$2)+1)

enter image description here

Upvotes: 1

Related Questions