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>

 

[63falcondude]

Try something like this. Everything (except H2) is formatted as Text to keep the leading 0's.

Excel 2010
	A	B	C	D	E	F	G	H
1	Ground	002	045	045	002		Zip Code	007
2		004	006	009	010		Ground	045
3		005	007	009	011
4					012
5					013
Sheet2

Cell Formulas
Range	Formula
H2	=INDEX(B1:E1,SUMPRODUCT(COLUMN(B2:E5)*(B2:E5=H1)))

 

[seguin85]

Try this:

=SUMPRODUCT(--(B2:M25=$O$1)*(B1:M1))/COUNTIF($B$2:$M$25,O1)

Where B2:M25 is where the data is located not including the Ground Shipping Location. O1 is where you enter your zip code and B1:M1 is the Ground Shipping Location.

 

[clau818]

Try this:

=SUMPRODUCT(--(B2:M25=$O$1)*(B1:M1))/COUNTIF($B$2:$M$25,O1)

Where B2:M25 is where the data is located not including the Ground Shipping Location. O1 is where you enter your zip code and B1:M1 is the Ground Shipping Location.

Thanks for this! So it seems like this formula works for any zipcode that begins with a 0 but for the ones that begin with 1-9, it is not returning the correct value. Any idea why this could be?

Thanks again!

 

[clau818]

Thanks for this! So it seems like this formula works for any zipcode that begins with a 0 but for the ones that begin with 1-9, it is not returning the correct value. Any idea why this could be?

Thanks again!

Sorry about that! I mean to send this to 63falcondude!

 

[clau818]

Try something like this. Everything (except H2) is formatted as Text to keep the leading 0's.

Excel 2010

	A	B	C	D	E	F	G	H
1	Ground	002	045	045	002		Zip Code	007
2		004	006	009	010		Ground	045
3		005	007	009	011
4					012
5					013

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula H2 =INDEX(B1:E1,SUMPRODUCT(COLUMN(B2:E5)*(B2:E5=H1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks for this! So it seems like this formula works for any zipcode that begins with a 0 but for the ones that begin with 1-9, it is not returning the correct value. Any idea why this could be?

Thanks again!

 

[clau818]

Try this:

=SUMPRODUCT(--(B2:M25=$O$1)*(B1:M1))/COUNTIF($B$2:$M$25,O1)

Where B2:M25 is where the data is located not including the Ground Shipping Location. O1 is where you enter your zip code and B1:M1 is the Ground Shipping Location.

Actually i'm having the same problem with your formula. It returns the right value for any zip code starting with 0's but when I try a zipcode starting with 1, it returns a 0. Also the value being returned is showing up as 2 or 3 or 4 rather than 002 or 003 or 004.

Again, thanks for your help!

 

[SpillerBD]

Just use LOOKUP.
(again make sure values are the same type, ie Text or numeric)

=LOOOKUP(H1,$B$2:$E$5,$B$1:$E$1)

 

[SpillerBD]

Just use LOOKUP.
(again make sure values are the same type, ie Text or numeric)

=LOOOKUP(H1,$B$2:$E$5,$B$1:$E$1)

Oh, and do not have blank cells. populate the blank cells with "000"

 

[clau818]

Oh, and do not have blank cells. populate the blank cells with "000"

Hi, thanks for this but I'm getting a #VALUE! error even after formatting all as text and filling in all blanks with 000.