Lookup From a Column based on Multiple Criteria (in Columns)

[Jon760]

Hi folks,

Below is a visual of my problem. I have zips all listed in one row / several columns B:F (all zips are unique), I would like to have zip in Column J lookup the value in Column A, if it finds a zip that are in columns B:F. Do you have any suggestion? or maybe how I can simply copy-transpose the zip and replicate the city with the number of instances so that I can do a simple vlookup?

Thank you for whatever help of thoughts you may have.

 

[JoeMo]

Here's a UDF you can try. Install the function then use it as in the example below. R is the entire data range, excluding the header row.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	City	Zip
2	Seattle	98001	98017	98057	98168	98010				97011	Portland
3	Portland	97007	97099	97011	97057	97001				92008	San Diego
4	San Diego	92101	92008	92084	92081	91919				98057	Seattle
Sheet6

Function ZipCity(R As Range, Zip As Long) As String
Dim Fnd As Range
Set Fnd = R.Find(Zip)
If Fnd Is Nothing Then
    ZipCity = ""
Else
    ZipCity = Cells(Fnd.Row, R.Columns(1).Column).Value
End If
End Function

 

[Jon760]

Here's a UDF you can try. Install the function then use it as in the example below. R is the entire data range, excluding the header row.
Sheet6

	A	B	C	D	E	F	G	H	I	J	K
City	Zip
Seattle				Portland
Portland				San Diego
San Diego				Seattle

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:26px;"><col style="width:19px;"><col style="width:16px;"><col style="width:42px;"><col style="width:68px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]98001[/TD]
[TD="align: right"]98017[/TD]
[TD="align: right"]98057[/TD]
[TD="align: right"]98168[/TD]
[TD="align: right"]98010[/TD]

[TD="align: right"]97011[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]97007[/TD]
[TD="align: right"]97099[/TD]
[TD="align: right"]97011[/TD]
[TD="align: right"]97057[/TD]
[TD="align: right"]97001[/TD]

[TD="align: right"]92008[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]92101[/TD]
[TD="align: right"]92008[/TD]
[TD="align: right"]92084[/TD]
[TD="align: right"]92081[/TD]
[TD="align: right"]91919[/TD]

[TD="align: right"]98057[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula K2 =zipcity($A$2:$F$4,J2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Function ZipCity(R As Range, Zip As Long) As String
Dim Fnd As Range
Set Fnd = R.Find(Zip)
If Fnd Is Nothing Then
    ZipCity = ""
Else
    ZipCity = Cells(Fnd.Row, R.Columns(1).Column).Value
End If
End Function

Thanks so much, it worked! Cheers to you man.

 

[Mike Szczesny]

Can you use something like this? You need to use Cntrl+Shift+Enter. Then copy down. Here is the formula [TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"] =INDEX($A$2:$A$4,SMALL(IF($B$2:$F$4=$J2,ROW($A$2:$A$4)-ROW($A$2)+1),1))

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 708"]
<colgroup><col span="10"><col></colgroup><tbody>[TR]
[TD]City[/TD]
[TD]Zip[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD="align: right"]98001[/TD]
[TD="align: right"]98017[/TD]
[TD="align: right"]98057[/TD]
[TD="align: right"]98168[/TD]
[TD="align: right"]98010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]97011[/TD]
[TD]Portland [/TD]
[/TR]
[TR]
[TD]Portland [/TD]
[TD="align: right"]97007[/TD]
[TD="align: right"]97099[/TD]
[TD="align: right"]97011[/TD]
[TD="align: right"]97057[/TD]
[TD="align: right"]97001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]92008[/TD]
[TD]San Diego[/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD="align: right"]92101[/TD]
[TD="align: right"]92008[/TD]
[TD="align: right"]92084[/TD]
[TD="align: right"]92081[/TD]
[TD="align: right"]91919[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]98057[/TD]
[TD]Seattle[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jon760]

Can you use something like this? You need to use Cntrl+Shift+Enter. Then copy down. Here is the formula [TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"] =INDEX($A$2:$A$4,SMALL(IF($B$2:$F$4=$J2,ROW($A$2:$A$4)-ROW($A$2)+1),1))
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 708"]
<tbody>[TR]
[TD]City[/TD]
[TD]Zip[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD="align: right"]98001[/TD]
[TD="align: right"]98017[/TD]
[TD="align: right"]98057[/TD]
[TD="align: right"]98168[/TD]
[TD="align: right"]98010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]97011[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD]Portland[/TD]
[TD="align: right"]97007[/TD]
[TD="align: right"]97099[/TD]
[TD="align: right"]97011[/TD]
[TD="align: right"]97057[/TD]
[TD="align: right"]97001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]92008[/TD]
[TD]San Diego[/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD="align: right"]92101[/TD]
[TD="align: right"]92008[/TD]
[TD="align: right"]92084[/TD]
[TD="align: right"]92081[/TD]
[TD="align: right"]91919[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]98057[/TD]
[TD]Seattle[/TD]
[/TR]
</tbody>[/TABLE]

Fabulous! The INDEX formula worked, thanks Mike. What if I simply copy and paste those cities (Column A) to Column G and do a lookup, what would the formula be?

 

[Mike Szczesny]

Well, as I see it, given only the city name (and no number to distinguish the city), how would would the formula know which number to find? For example, the formula will recognize that 97007, 97099, 97011, 97057, and 97001 are tied to Portland.