CODEWITHSUNDEEP
excelexcel-formula
anthonyfrancqq
anthonyfrancqq

Reputation: 13

Excel - XLOOKUP with Multiple Results

I have a spreadsheet that contains counties, zip codes, and recording fee. Some zip codes contain more than one county.

For example Zip Code 1314 contains the following counties -- Cortland and Onondaga. I am trying to have excel display both of those once the zip code has been entered and their corresponding fee.

I have attached two images, one is Expected outcome, the other is what I currently have.

Here is a link to the spreadsheet -- https://file.io/4ifrOghDn7ss

Expected Result: Expected Result

Current Result: Current Result

Upvotes: 1

Views: 15839

Answers (2)

Kevin Holliday
Kevin Holliday

Reputation: 96

If your lookup value was in A19, Put this formula in B19:

=FILTER(
        FILTER(ZipCode!A2:H53963,ZipCode!A2:A53963='County Lookup by Zipcode'!A19),
        {FALSE,FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,TRUE}
       )

The outer filter controls the columns that are displayed. You may need to change the TRUE|FALSE values to control which columns are displayed or not.

Two things, this is not the column order you want, so you will have to change the order on Zip Code tab. Also the cells on your spreadsheet are formatted as text, so be sure to change the formating of the input cell to be general or a number.

Example of FILTER Formula

Upvotes: 0

urdearboy
urdearboy

Reputation: 14580

You can use the FILTER function to return all values associated with a input. See a sample input table (table on top) along with a output table (bottom) where the only input is cell A9.

With this set up, just enter the formula on cell B9 which will import the desired range

=FILTER(B2:E4,A2:A4=A9,"")

enter image description here

Upvotes: 3

Related Questions