return multiple items for one lookup value for a table

**Crawfy** · 04-02-2017, 08:40 PM

I am trying to return multiple items for one lookup value for a table.

I have a list of staff details in one sheet (From Adobe) that is derived form another simpler set of data in another sheet (Raw Data) within the same workbook. This is done so that raw data can be imported without corrupting conditional formatting in the main working sheet.

I have created a crude sheet (Vlookup) to retrieve information based on a payroll number or name search. I managed to get exact matches returned.

My next step is to return the list of all possible matches to a non-exact name search. If I am looking for "Pat" but "Patricia" comes first, I currently get "Patricia"s details. I have included the formula for an array that I have been working on in the uploaded file.

On the Vlookup Sheet I would like to return the results in cells B12:Q12 and down. I would like this to stop once there are no more matching names.

I followed the tutorial at https://www.youtube.com/watch?v=Tp7I...pxGa2QYbywKz8O
If I change the formula from an array formula after using Ctrl + Shift + Enter to a regular formula using Enter the result returned is always the 12th name form the From Adobe sheet.

The uploaded file has been desensitized by creating random names & details.

**JBeaucaire** · 04-02-2017, 09:07 PM

The way I handle this is to avoid arrays, which seem fun, but get cumbersome. So, in your scenario where you always only need ONE set of multiple lookup values, I add a KEY column to my data to facilitate this list and keep the workbook at top speed.

So, on the ADOBE sheet, add a KEY in Q, first formula in Q2, then copied down:
=IF(ISNUMBER(SEARCH(LookName,C2)),N(Q1)+1,N(Q1))

Then, on the LOOKUP sheet (change the name from VLOOKUP), the first non-array formula in B12, copied down and across:
=IF(ROWS(B$12:B12)>$A$4,"",INDEX('From Adobe'!$1:$5000,MATCH(ROWS(B$12:B12),'From Adobe'!$Q:$Q,0),MATCH(B$7, 'From Adobe'!$1:$1, 0)))

Notice this version is looking at the headers as well, so this means you can organize this new table in any order you want, a superior result. Change a header and the same formula just keeps working.

**Crawfy** · 04-02-2017, 09:19 PM

That has worked and yes, it is far more elegant. Thank you so much for the fast reply and the solution.
So I learn from this, what is the purpose of the KEY?
The formula on the ADOBE sheet appears to workout the data where the KEY changes.
I certainly need to learn more about index and match.

**JBeaucaire** · 04-02-2017, 09:26 PM

An INDEX is merely an ordered list of possible values to return, the second INDEX parameter is what position in the index you want to see. So the KEY is creating a unique index number for each match, the repeating numbers are ignored, only the first instance of each key value is used.

Your COUNTIF already determines only 22 matches exist, so we merely go and retrieve the INDEX value from each of the 22 unique values listed in the new KEY column.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Crawfy** · 04-02-2017, 10:11 PM

thank you for all the help and information.

return multiple items for one lookup value for a table

LinkBack

Thread Tools

Rate This Thread

Display

return multiple items for one lookup value for a table

Re: return multiple items for one lookup value for a table

Re: return multiple items for one lookup value for a table

Re: return multiple items for one lookup value for a table

Re: return multiple items for one lookup value for a table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Return Multiple Items with One Lookup Value

[SOLVED] Lookup function used to return multiple values corresponding to a specific word in a table

[SOLVED] Multiple Lookup to return value in table

lookup a single value and return items in multiple columns

[SOLVED] Return multiple items from two and three lookup values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions