CODEWITHSUNDEEP
excelexcel-formula
DLark
DLark

Reputation: 17

Finding which list (of multiple) a value is in

I have several lists of general categories in different columns, with each category title in row 1 and each having several different items in rows below it. Picture for reference:

enter image description here

On this same worksheet I want to be able to enter any of the items (Pork, Apple, Cheese, etc.) in cell E2 (my lookup value). What formula can I put in cell F2 to have it return the header of the list that item belongs to (Meat, Fruit, Dairy, etc.)? With my current understanding of V/HLOOKUP and INDEX/MATCH formulas I believe you can only lookup values in a single row or , but here I want to be able to have a lookup array of multiple rows and columns, and have it return the value of the top row of the appropriate column.

Edit:

Here is what I have tried so far:

=HLOOKUP(E2,A1:D5,1,FALSE)
=INDEX(A1:D5,1,MATCH(E2,A2:D5,0))

I am using Excel 2016

Upvotes: 1

Views: 129

Answers (2)

PeterT
PeterT

Reputation: 8557

The answer is to use SUMPRODUCT.

If your lookup value is in E2 and your formula in F2, use this formula:

=INDEX(A1:D1,SUMPRODUCT((A2:D5=E2)*COLUMN(A2:D5)))

This formula is not an array formula, and doesn't require the ctrl+shift+enter.

Upvotes: 2

P.b
P.b

Reputation: 11483

=INDEX(A1:D1,,MIN(IF(E2=A2:D5,COLUMN(A:D)))) Enter the formula with ctrl + shift + enter

MIN searches for the first TRUE where E2=A2:D5 and returns in which column A:D that was and returns that number for use in your INDEX.

Upvotes: 2

Related Questions