Match multiple Columns (Advanced)

[on3moy]

Hello Community,

I am trying to match a selected cell to a reference of multiple columns. For my example, I want to choose a state and I want it to return which category it comes from. Each category has multiple unique states. Ex. I search for CA within whole time, I want it to return me the column name..Any feedback is appreciated.

---

SW	MW	CS	SE	WS	NW	FL	NE	OTH
AR	IA	IN	AL	AZ	ID	FL	DC	CT
LA	IL	KY	GA	CA	OR		DE	MA
OK	KS	MI	MS	CO	WA		MD	ME

<tbody>
</tbody>

 

[sandy666]

something like that?

with PowerQuery

SW

MW

CS

SE

WS

NW

FL

NE

OTH

Lookup

Result

AR

IA

IN

AL

AZ

ID

FL

DC

CT

DE

NE

LA

IL

KY

GA

CA

OR

DE

MA

OK

KS

MI

MS

CO

WA

MD

ME

 

[on3moy]

Yes!

 

[sandy666]

But you'll need PowerQuery (aka Get&Transform) (Excel 2010/2013 - add-in, Excel 2016 and higher - built-in)
Do you have PowerQuery?

 

[on3moy]

Yes

 

[sandy666]

sure

here is a link to the example excel file

edit:
btw. if you change lookup value use Ctrl+ALt+F5 or right click on green table and choose Refresh

 

[on3moy]

Yasss, Thank you! Very much appreciated!

 

[sandy666]

you are welcome

if eg. CA will be in two or more columns result will give you more names of column