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.

---

[TABLE="width: 500"]
<tbody>[TR]
[TD]SW[/TD]
[TD]MW[/TD]
[TD]CS[/TD]
[TD]SE[/TD]
[TD]WS[/TD]
[TD]NW[/TD]
[TD]FL[/TD]
[TD]NE[/TD]
[TD]OTH[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]IA[/TD]
[TD]IN[/TD]
[TD]AL[/TD]
[TD]AZ[/TD]
[TD]ID[/TD]
[TD]FL[/TD]
[TD]DC[/TD]
[TD]CT[/TD]
[/TR]
[TR]
[TD]LA[/TD]
[TD]IL[/TD]
[TD]KY[/TD]
[TD]GA[/TD]
[TD]CA[/TD]
[TD]OR[/TD]
[TD][/TD]
[TD]DE[/TD]
[TD]MA[/TD]
[/TR]
[TR]
[TD]OK[/TD]
[TD]KS[/TD]
[TD]MI[/TD]
[TD]MS[/TD]
[TD]CO[/TD]
[TD]WA[/TD]
[TD][/TD]
[TD]MD[/TD]
[TD]ME[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

something like that?

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]SW[/td][td=bgcolor:#5B9BD5]MW[/td][td=bgcolor:#5B9BD5]CS[/td][td=bgcolor:#5B9BD5]SE[/td][td=bgcolor:#5B9BD5]WS[/td][td=bgcolor:#5B9BD5]NW[/td][td=bgcolor:#5B9BD5]FL[/td][td=bgcolor:#5B9BD5]NE[/td][td=bgcolor:#5B9BD5]OTH[/td][td][/td][td=bgcolor:#5B9BD5]Lookup[/td][td][/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AR[/td][td=bgcolor:#DDEBF7]IA[/td][td=bgcolor:#DDEBF7]IN[/td][td=bgcolor:#DDEBF7]AL[/td][td=bgcolor:#DDEBF7]AZ[/td][td=bgcolor:#DDEBF7]ID[/td][td=bgcolor:#DDEBF7]FL[/td][td=bgcolor:#DDEBF7]DC[/td][td=bgcolor:#DDEBF7]CT[/td][td][/td][td=bgcolor:#DDEBF7]DE[/td][td][/td][td=bgcolor:#E2EFDA]NE[/td][/tr]

[tr=bgcolor:#FFFFFF][td]LA[/td][td]IL[/td][td]KY[/td][td]GA[/td][td]CA[/td][td]OR[/td][td][/td][td]DE[/td][td]MA[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]OK[/td][td=bgcolor:#DDEBF7]KS[/td][td=bgcolor:#DDEBF7]MI[/td][td=bgcolor:#DDEBF7]MS[/td][td=bgcolor:#DDEBF7]CO[/td][td=bgcolor:#DDEBF7]WA[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]MD[/td][td=bgcolor:#DDEBF7]ME[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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