CODEWITHSUNDEEP
excelexcel-formula
Bill
Bill

Reputation: 91

Excel: Lookup matching cell in a table and return the value of the first cell in the row

Here is a picture to demonstrate:

enter image description here

So from this, we can see Item 9 belongs to Category C. I want to find a formulas to automate this process. Where if I provide excel with a number, say 12, it will be able to give me "D."

Unfortunately, this is not possible with VLookUp nor HLookUp as they require the matching value to be on the left side of the table. I've tried with Index / Match, but that seems to spit out an error instead.

Am I approaching this wrong? Should I reformat the table or is there a better formula to use?

Upvotes: 0

Views: 901

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use INDEX with AGGREGATE:

=INDEX(A:A,AGGREGATE(15,6,ROW($B$2:$E$5)/($B$2:$E$5 = 12),1))

You can change the 12 to a cell reference to make it more dynamic.

enter image description here

Upvotes: 4

Related Questions