Returning multiple distinct/repeating values for vlookup or index-match

Hello everyone, first time poster and rookie excel user.

I am trying to create either a vlookup formula or a index match formula to match a specific id number with a corresponding location code stored on a separate worksheet within the same workbook, however each unique id may have one or more locations associated with it and it may also have multiples of the same location numbers associated with it. I would like the workbook to return all values in adjacent cells. Is there a way to do that? I have attempted searches and following previous directions but I have been unable to make it work.

I am attaching a sample workbook with the data

I am hoping to have results display in columns G, H, I, ect on the "Match Sheet" based on how many different locations are associated with the specific ID number from the "Data Sheet"

for example person Id 16209 should return two different location codes, 407 and 210 from the "data sheet" while personid 16284 should return two of the same location codes, 457 and 457

any help would be greatly appreciated the boss handed this off to me and it seems a bit over my head at the time

Thank you all in advance.

Tony, thank you! That worked perfectly, its calculating exactly what I need.

One small side issue tho, it has made the workbook virtually unusable, I tried turning off autocalc and it still takes forever to do anything with the data including scrolling around. Is there anything that can be done to speed up the sheet?

The formula that was suggested is used in 5 columns that are about 800 rows each and is searching a sheet that contains 160K rows of two column data. Is this the most efficient/only way to do this? I'm concerned that once it is complete any reload of the data will cause a crash and render it useless.

Thanks for you help guys!

Hello,

I would need your help on he following please.

in tab : report- I'll choose the country.
in tab : Extract ESr: data will be changed accordingly to the selection.

and data will be cleaned in column T-U- V- W.

I need kind of macro in Column X (or formula), to extract unique value from column W. Without duplicates,

This will allow me to make a reference in Tab report to put this code into a table without duplicates.

Just i forget to mentioned that i need the macro to run each time we change the selection of the country.



Thank you for your help.

Sorry for this.

I'll do

Sorry for this.

I'll do

Hello everyone,

I know it'S an old question but i have a similar issue regarding payroll.

Sheet 1 has the input including date, name and order#

I have 1 sheet for every employee that has a 2 week table in a row and a column for the order#

I was able to pull all the data with 2 criteria from Input sheet into each employees sheet
The criteria are range of dates and employee name and the return is all the work# that match those criteria

I used formula below which is similar to the one listed above:

=IFERROR(INDEX(Input!$A$2:$H$999, SMALL(IF(COUNTIF($C$22:$O$22, Input!$B$2:$B$999)*COUNTIF($D$10, Input!$D$2:$D$999), ROW(Input!$A$2:$H$999)-MIN(ROW(Input!$A$2:$H$999))+1), ROW(A1)), COLUMN($C$1)), "")

The problem I'm running into is that it's returning duplicates of the same work#.

How can I add a function to remove duplicates? I do not want to use the filter function as I want it to be done automatically.

please help!