Multiple search criteria within the same spreadsheet

[Lore0818]

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.

 

[Saba Sabaratnam]

Hi

Based on my understanding of your requirement, you could use the following formulas

Enter the following formula in j19 to remove the comma (,)

=LEFT(I18,5)

Enter the following formula in F1

=XLOOKUP(LEFT(D1,5),$J$18:$J$19,$J$18:$J$19,"Not Found")

If the particular code is not found, the formula result will say "Not Found".

Kind regards

Saba

 

[Lore0818]

Thank you Saba. Would you kindly help me futher?
I tried to modify the formula to fit the cells that are on the report. I removed the comma from the zip codes because I had added them to begin with.
What do I need to change in the formula to make it work with what I have here? (I was trying to copy the values as per your example)
Will the search highlight or display only the rows with the values in my search (all zip codes in column J3 through J57)?
The rows go through 2308.

 

[Saba Sabaratnam]

No problem

Modify your formula to XLOOKUP(LEFT(D1,5),$J$3:$J$57,$J$3:$J$57,"Not Found") and copy the formula in each of cells from F2 to F2308.

Left (D1,5) is to extract the first five digits of value in Cell D1

The formula will bring the zip code if the code in J3:J57 exist in any of cells from D1 to to D2308.

If any of code in J3:j57 does not exist in D1 to to D2308 , the formula will produce "Not Found".

Kind regards

Saba

 

[Lore0818]

Thank you again. That made something happen but everything shows as 'not found'.
I've highlighted exmaples that are suppose to have been selected from the list of zip codes I'm searching J52 (78753) & J56 (78758)
What did I miss?

 

[Saba Sabaratnam]

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

Use the above formula which multiplies LEFT(D1,5) by 1 to turn this text into number as your data in J3:j57 seem to be number.

Kind regards

Saba

 

[Lore0818]

good morning Saba.
I'm getting a message with that formula.
I was trying to upload the mini-sheet add-in....lets just saw that I should takle one thing at a time.

 

[Lore0818]

**say, not saw.

 

[Saba Sabaratnam]

Use this formula please.


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

I believe this error was caused by a space after LEFT(D1,5).

Kind regards

Saba

 

[Lore0818]

well, I saw that and tried it already without the space. It's now telling me #VALUE!