How to lookup a value across multiple columns and return Column Name

[SnuffNCHess]

I have a listing of zip codes in Column B that i need to compare against 4 different columns (D, E, F, G)

I have named the ranges for each of the 4 columns.... Let's call the names "NAMED", "NAMEE", "NAMEF", "NAMEG"

WITHOUT VBA, What i need to happen is then in cell C5 to appear the correct value for the below situation

If B5 appears in range, NAMED, then display "NAMED"
Else, if B5 appears in range NAMEE, then display "NAMEE"
Else, if B5 appears in range NAMEF, then display "NAMEF"
Else if B5 appears in range NAMEG, then display "NAMEG"
Else, return "0"

Other things will happen to the value in C5 later on... but for now, just trying to get this basic obstacle resolved.

Thanks!!

 

[Chihiro]

Assuming Headers are in range D1:G1

And data range for columns D2:G5

In C5: =IFERROR(INDEX($D$1:$G$1,SUMPRODUCT(($D$2:$G$5=B5)*COLUMN($D$2:$G$5))-COLUMN(C:C)),0)

Copy down

 

[Luke M]

Hello, and welcome to the forum SnuffNCHess!

You can't return the name of a Named Range, but presuming that the column header will suffice, you can do this array formula:
=IFERROR(INDEX($1:$1,(1/MIN(IF($D$2:$G$10=B2,COLUMN($D$2:$G$10))))^-1),0)

 

[SnuffNCHess]

Thank you Luke and Chihiro.

This is working almost perfectly.

The bug that I am running into is on zip codes that start with a "0" now. if i set the info in D2:G10 to "zip code" so that 5 digits show for the zip, the proper message is not returned on C5

I tried changing d2:G10 to Text instead of number, and that also does not work.

Do you have any thoughts on how to get around this???

 

[Chihiro]

Hmm, can you upload a sample? Both formulas should work with your scenario.

See attached with Column C & Range D2:G5 formatted as Zip Code (US)