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.

Ground	002	045	045	002	003	002	003	004	003	004	003	002
	004	006	009	010	014	015	017	043	045	046	050	052
	005	007	009	011	014	016	018	044	045	047	051	053
				012			019			048
				013			020			049
							021
							022
							023
							024
							025
							026
							027
							028
							029
							030
							031
							032
							033
							034
							035
							036
							037
							038
							039
							040
							041
							042

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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!