Complex look up formula needed

[clau818]

Hi all,

I have a sheet where the header row are Ground Shipping Locations and the numbers beneath it are the first 3 numbers of zip codes that pertain to that ground shipping location.

I need a formula that will return the Ground Shipping location upon entering the zipcode. The issue I am having is that the ground shipping locations appear multiple times in different columns. See an example below. Please HELP! and thanks in advance! The formula I put together which is not working is =INDEX(Sheet2!A1:DX78,1,SMALL(IF(Sheet2!A1:DX78=I3,COLUMN(Sheet2!A1:DX1),99*99),1)) where 'sheet 2' is where the data below is located and cell I3 is where I am entering the zipcode on a separate sheet.

The top row is the header row with the ground shipping locations. The numbers underneath are the zip codes associated with that ground shipping locations.

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Ground[/TD]
[TD="class: xl64, width: 64"]002[/TD]
[TD="class: xl64, width: 64"]045[/TD]
[TD="class: xl64, width: 64"]045[/TD]
[TD="class: xl64, width: 64"]002[/TD]
[TD="class: xl64, width: 64"]003[/TD]
[TD="class: xl64, width: 64"]002[/TD]
[TD="class: xl64, width: 64"]003[/TD]
[TD="class: xl64, width: 64"]004[/TD]
[TD="class: xl64, width: 64"]003[/TD]
[TD="class: xl64, width: 64"]004[/TD]
[TD="class: xl64, width: 64"]003[/TD]
[TD="class: xl64, width: 64"]002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]004[/TD]
[TD]006[/TD]
[TD]009[/TD]
[TD="class: xl65"]010[/TD]
[TD]014[/TD]
[TD]015[/TD]
[TD]017[/TD]
[TD]043[/TD]
[TD]045[/TD]
[TD]046[/TD]
[TD]050[/TD]
[TD]052[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]005[/TD]
[TD]007[/TD]
[TD]009[/TD]
[TD="class: xl65"]011[/TD]
[TD]014[/TD]
[TD]016[/TD]
[TD]018[/TD]
[TD]044[/TD]
[TD]045[/TD]
[TD]047[/TD]
[TD]051[/TD]
[TD]053[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"]012[/TD]
[TD][/TD]
[TD][/TD]
[TD]019[/TD]
[TD][/TD]
[TD][/TD]
[TD]048[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"]013[/TD]
[TD][/TD]
[TD][/TD]
[TD]020[/TD]
[TD][/TD]
[TD][/TD]
[TD]049[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]021[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]022[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]023[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]024[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]026[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]027[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]028[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]029[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]030[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]031[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]032[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]033[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]034[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]035[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]036[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]037[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]038[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]039[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]040[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]041[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]042[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

If you go to cell formatting and switch from a number format to Text, that does not change the data from a number to Text.
With your data being digits only, Excel should give a Green corner warning that the data is a number stored as text or preceded by a single quote.
When entering the lookup value, it too must be entered as text, which I prefer to force by typing the preceding single quote.
Alternatively you could force the lookup value to be TEXT(H1,"000")

You should still be able to use all numeric data formatted as "000".
(I do not like using numbers for none mathematical values like zip codes, but that is a personal preference.)

 

[Mathias Pedersen Heinze]

This should do the trick as well. Since it's an array formula, remember to close it with Ctrl + Shift + Enter.

=INDEX(B1:M1,1,SUM(IF(Q5=B2:M27,COLUMN(B2:M27),0))-COLUMN(B1)+1)

 

[clau818]

This should do the trick as well. Since it's an array formula, remember to close it with Ctrl + Shift + Enter.

=INDEX(B1:M1,1,SUM(IF(Q5=B2:M27,COLUMN(B2:M27),0))-COLUMN(B1)+1)

Hi Mathias, what is the Q5 supposed to be in this case?

 

[Mathias Pedersen Heinze]

Hi Mathias, what is the Q5 supposed to be in this case?

Q5 is the ZIP Code that you want to look up (for instance 047). Everything except Q6, in which I have the formula, is formatted as text.

 

[clau818]

Thank you all so much! It finally worked!

The problem was that all my zip codes that started with 0's were appearing with the green flag and all the zip codes starting with 1-9 were not. I didn't realize this until SpillerBD told me about the formula to convert numbers to text, which I was unaware of. Even if I highlight the whole sheet and converting it all to text using the formatting function, this was not resulting in the zip codes 1-9 appearing with the green fla. After using the formula =Text(H1,"000") and copying paste special values the results over my original data, they appeared with the green flags. I tried all of your formulas now and they all worked! So i decided to go with the simplest one which was SpillerBD's simple Lookup formula.

Again thank you all! You're all excel geniuses and I strive to some day be as proficient in excel as you guys!