Multiple search criteria within the same spreadsheet

Lore0818

New Member
Joined
Jul 6, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hello all,
I'm sure you can tell I'm a newbie to excel by the thread I'm posting. I've read so many posts on multiple searches, however I couldn't find any that fits what I'm looking for. Maybe I'm tired and just can't see or think outside the box.
It's simple what I'm trying to do and would be soooooo helpful.

I'm trying to look up multiple zip codes at the same time in one spreadsheet.
I have found online where I can group all the zip codes within a 25 mile radius of a specific location.
Now, I'm trying to lookup all those 20+ zip codes on a spreadsheet which contains client info including their zip code (in a separate column)
Column I containes the zip codes I'm trying to search in the spreadsheet of about 2000+ rows of data.

Thank you advance for any help.

1625610238082.png
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
Try this formula. Copy this formula as it is in F2 and copy it down.

=XLOOKUP(LEFT(TRIM(D1),5)*1,$J$3:$J$57,$J$3:$J$57,"Not Found")

if it does not work, copy a data point (say D2) as text so that I can see the structure of your data.

Kind regards

Saba
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Lore0818

New Member
Joined
Jul 6, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I'm sorry, I don't understand what you're asking me to do.
Is this what you're asking?
I promise I won't get offended if you give me instructions as if I was 4 years old. :)

Lead TypeCountyStateZipCityNot Found
DM-SPDALLASTX75041-4200Garland
#VALUE!​
DM-SPDALLASTX75051-2944Grand PrairieNot Found
DM-SPDALLASTX75060-4127IrvingNot Found
DM-SPNAVARROTX75110-7136CorsicanaNot Found
DM-SPVAN ZANDTTX75117-5195EdgewoodNot Found
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
no problem :)


You could try this approach.

Enter the following formula in F2 and copy it down to extract first five digits of your code and turn it to number.

=TRIM(CLEAN(LEFT(D1,5)))*1

Then this formula in G2 and copy it down.

=XLOOKUP(F1,$K$3:$K$57,$K$3:$K$57,"Not Found")

Kind regards

Saba
 

Lore0818

New Member
Joined
Jul 6, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
These are my results.

1625677735158.png
 

Attachments

  • 1625677696575.png
    1625677696575.png
    73.6 KB · Views: 1

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365

ADVERTISEMENT

Try this formula by coping in F1 as it is.

=IF(SUM(IF(ISNUMBER(SEARCH(IF($I$3:$I$57<>"",$I$3:$I$57,NA()),D1,1)),SEARCH(IF($I$3:$I$57<>"",$I$3:$I$57,NA()),D1,1),0))>0,LEFT(D1,5),"Not Found")

Then copy F1 and paste the formula from f2 to the last row of data of column F.

Kind regards

Saba
 

Lore0818

New Member
Joined
Jul 6, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
These were my results "not found" all the way down the F column. I even checked every so many rows all the way down to make sure it had been copied all the way through.
1625696117989.png
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365

ADVERTISEMENT

It is referencing to wrong lookup reference.

Use this formula .

==IF(SUM(IF(ISNUMBER(SEARCH(IF($J$3:$J$57<>"",$J$3:$J$57,NA()),D1,1)),SEARCH(IF($J$3:$J$57<>"",$J$3:$J$57,NA()),D1,1),0))>0,LEFT(D1,5),"Not Found")

Kind regards

Saba
 
Solution

Lore0818

New Member
Joined
Jul 6, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
if won't let me use 2 equal signs..... ==
So I took one out and it displays 'not found'
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
Only single = sign please.

Include 75041 in J3 (replace 76511 with this number) to see if the formula produces 75041 or
"Not found" result.

Also can you please confirm that you have office 365?

Kind regards

Saba
 

Lore0818

New Member
Joined
Jul 6, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Yes...Yes...YES!!!!!
The last formula worked. I created a filter in column F (which I should've done from the beggining) and it displayed what I was looking for.
Thank you SOOOOOOOO much Saba.

1625759685945.png
 

Forum statistics

Threads
1,148,283
Messages
5,745,842
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top