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]

 

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]002[/TD]
[TD="align: center"]045[/TD]
[TD="align: center"]045[/TD]
[TD="align: center"]002[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Zip Code[/TD]
[TD="align: center"]007[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]004[/TD]
[TD="align: center"]006[/TD]
[TD="align: center"]009[/TD]
[TD="align: center"]010[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Ground[/TD]
[TD="align: center"]045[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]005[/TD]
[TD="align: center"]007[/TD]
[TD="align: center"]009[/TD]
[TD="align: center"]011[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]012[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=INDEX(B1:E1,SUMPRODUCT(COLUMN(B2:E5)*(B2:E5=H1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.