VLOOKUP return multiple values

CaulfieldS · Feb 2nd 2017

Hey everyone, im looking to create a group of cells to search a table and return what it finds, in my case there may be multiple results.

here is the formula im using that searches the table for text in a certain cell then returns text from the cell next to the keyword

=IF(ISNA(VLOOKUP(a3, ai2:ak34, 2, FALSE)),"",VLOOKUP(a3, ai2:ak34, 2, FALSE))

a3= desired search keyword
ai:ak34 = the table i want to search
2=what column in the table i want the search to return with

like i mentioned my issue comes down to when the text in "a3" is mentioned more than once, the only answer i get is the first one it finds, and in my case it would be inconvenient to break down the table and make multiple searches.

Thank you in advance

richadj4 · Feb 2nd 2017

Re: Search table return multiple value's

1) are you searching multiple columns for the text in A3? (as written this would only return a result if the A3 text is in column AI)

2) Do you have an upper bound on the number of results?

3) Is column AI sorted, or can it be?

4) Are you looking for a formulae solution (possible but difficult-very difficult depending on above answers) or is VBA acceptable?

5) sample data?

CaulfieldS · Feb 3rd 2017

Re: Search table return multiple value's

Quote from richadj4;784720

1) are you searching multiple columns for the text in A3? (as written this would only return a result if the A3 text is in column AI)

2) Do you have an upper bound on the number of results?

3) Is column AI sorted, or can it be?

4) Are you looking for a formulae solution (possible but difficult-very difficult depending on above answers) or is VBA acceptable?

5) sample data?

Display More

Thank you for your reply, i have not ventured into using VBA's as of yet, this may be the tipping point in starting to learn and use them. to answer your questions,

1) the text im searching against (a3) will only show up in column AI, one set of answers im looking for will be in column AJ, i intend to adapt whatever answer i get to also get text from AK.
2) im not sure i understand? if your refering to the maximum number of results probably no more then two or three.
3) Column AI through AK is a populated directly from another worksheet, so i dont believe (at my novice level) that i can sort.
4) my original hope was a formula based solution, but like i mentioned before that if this is the tipping point to getting into VBA's so be it

now for some sample data

[TABLE="width: 500"]

[tr]

[td][/td]

[td]

Column A

[/td]

[td]

column b

[/td]

[td]

column c

[/td]

[td]

column d

[/td]

[td]

skip

[/td]

[td]

column AI

[/td]

[td]

column AJ

[/td]

[td]

column AK

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td][/td]

[td]

Names

[/td]

[td]

Horse

[/td]

[td]

Time

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

Name

[/td]

[td]

Horse

[/td]

[td]

Time

[/td]

[td][/td]

[td]

Jane

[/td]

[td]

Shilo

[/td]

[td]

8:30

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

Jane

[/td]

[td]

Shilo

[/td]

[td]

8:30

[/td]

[td][/td]

[td]

John

[/td]

[td]

Medoro

[/td]

[td]

9:15

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td][/td]

[td]

George

[/td]

[td]

10:00

[/td]

[td][/td]

[td]

Jane

[/td]

[td]

George

[/td]

[td]

10:00

[/td]

[/tr]

[tr]

[td]

5

[/td]

[td][/td]

[td]

Joe

[/td]

[td]

Fidoro

[/td]

[td]

10:45

[/td]

[/tr]

[/TABLE]

So my ideal situation A3 is populated from another worksheet, the same goes for AI2:AK5. Thus triggering b3:c5 to get populated by all instances where A3 is found in AI2:AK5

Hope this information helps! Thanks again for the help

richadj4 · Feb 3rd 2017

Re: Search table return multiple value's

Okay, Yes this is possible with formulas, quick and dirty, this ARRAY formula in B3:
=IFERROR(OFFSET($F$1,SMALL(IF($F$2:$F$5000=$A$3,ROW($F$2:$F$5000)-ROW(INDEX($F$2:$F$5000,1,1))+1),ROW()-2),COLUMN()-1),"")
(Note ARRAY formulas MUST be enter with Ctrl Shift Enter, so paste the above text into that cell, and instead of pressing enter, press ctrl + shift + enter)

Then copy this formula across 1 and down "several" (If you are think 2 - 3 answers, copy it down 6 lines to be safe)

Things to be aware of:
This will handle up to 5000 lines of data in the F-H table. you SHOULD be using dynamic named ranges instead of absolute addresses (which I can explain if needed), but this will get the job done.
Happy to describe a detailed explanation of the formula IF YOU WANT IT
It MAY look like it's not working as you get a value like 0.354 in the time column, this is just formatting, it's displaying 8:30 as a decimal number, just change the format in this column (or copy paste format only from the time column)

CaulfieldS · Feb 3rd 2017

Re: Search table return multiple value's

So with this formula i can alter "5000" down to the actual size of the table ill be searching through, as even when the table gets altered it will not get any larger than what it is? if i then want to return the time value i change the 2 in bold?($F$2:$F$5000,1,1))+1),ROW()-2),COLUMN()-1),"")
or would it be one of the "1's" ?

What do you mean by dynamic ranges instead of absolute addresses? if you have time to explain i have the time to learn something new.

Thank you for all your help Richadj4 i will be entering this into my workbook shortly to test out.

CaulfieldS · Feb 3rd 2017

Re: Search table return multiple value's

I attempted this formula two ways, altering to fit my spreadsheet with no luck, as well as altering my spreadsheet to mimic my table above again with no luck. is it worth noting that im running excel 2003? (13 year old program i know, but it's suiting my needs for work)

richadj4 · Feb 3rd 2017

Re: Search table return multiple value's

1) yes the 5000 can be altered to a bigger number. It can safely be bigger than the table.
2) don't futz with the "2"!
3) This formula will return the Horse if it is in column B and the time if it is column C (the column()-1 indicates where it returns things from)
4) Is it currently working at all? I'd make sure it is working correctly re the sample you uploaded. This is obviously very small data, but when you can make it work there, you should be able to make it work elsewhere.

5) Dynamic named ranges are AWESOME. There is a very good article on them somewhere on this site. In brief a named range (you may already know) describes a range of cells, so instead of saying $F$2:$F$5000 you can say ThisStuff. Dynamic named ranged goes further by making ThisStuff change size depending on what's in it. You set up a dynamic named ranged in the name manager, and the formula in this case would be "=OFFSET($F$1,1,0,COUNTA($F:$F)-1,1)"

I wouldn't recommend trying this until you are making it work correctly with a fixed range.

richadj4 · Feb 3rd 2017

Re: VLOOKUP return multiple values

Also, potentially stupid remark, make sure everytime you are entering or editing the formula, you are doing it with ctr shift enter. A quick way to check, is when you have entered it, if you see the formula in the formula bar, it SHOULD be surrounded in {} to indicate it is an array formula.

CaulfieldS · Feb 4th 2017

Re: VLOOKUP return multiple values

Hey Richadj4, turns out the version of excel on my work computer is the cause for that formula not working, just loaded my book on my personal laptop and tried that code with excel 2016 and works perfect.

richadj4 · Feb 4th 2017

Re: VLOOKUP return multiple values

Interesting.
I'm not sure WHICH part of that is not valid in 2013. Array formulas definitely work. It MAY be the iferror function, I'm not 100% sure when this was introduced. Try it without the ifferror. it will still work, but you will get #N/A returns AFTER the valid information (so if there are 2 instances to be returned, it WILL return those, but lines 3 through 6 will be #N/A, There are other more complex ways to get around this, IF this is in fact the issue.)

CaulfieldS · Feb 24th 2017

Re: VLOOKUP return multiple values

So i was able to make the following work for me

=OFFSET(F1,SMALL(IF(F2:F5000=A3,ROW(F2:F5000)-ROW(INDEX(F2:F5000,1,1))+1),ROW()-2),COLUMN()-1)

note im using excel 2003!

However for some reason i cannot seem to adjust the formula to search a new table, my end goal is to have this formula on "sheet 1" searching for text on "sheet 1" in a table on "sheet 2", but again im stuck at even shifting from one table to another on the same page

**Carim** · Feb 25th 2017

Re: VLOOKUP return multiple values

Hello,

From what I understand ... it seems to me the first issue is to generate a Unique Lit of Names starting in A3 ...

Am I wrong ???

CaulfieldS · Mar 2nd 2017

Re: VLOOKUP return multiple values

Currently A3 is populated by one name, with nothing below it. My issue is that i was able to put the formula above into a worksheet in my 2003 excel. populate a table using column F filled with names, Column G filled with items, and column H filled with order dates. However when i try to move the "search area" from column "F" to another column lets say column "L" i dont get any results and i cant figure out why.

**NBVC** · Mar 2nd 2017

Re: VLOOKUP return multiple values

Can you show us the formula you tried?

You should keep all the $ signs as per the original formula given. This makes the ranges absolute (fixed) so that you can copy formula down and/or across with affecting lookup references..

CaulfieldS · Mar 3rd 2017

Re: VLOOKUP return multiple values

NBVC This is the formula i was able to make work,

=OFFSET(F1,SMALL(IF(F2:F5000=A3,ROW(F2:F5000)-ROW(INDEX(F2:F5000,1,1))+1),ROW()-2),COLUMN()-1)

For some reason (i suspect that its excell 2003) when using the "$" signs the code does not work since the following does not work

=OFFSET($F$1,SMALL(IF($F$2:$F$5000=$A$3,ROW($F$2:$F$5000)-ROW(INDEX($F$2:$F$5000,1,1))+1),ROW()-2),COLUMN()-1)

that being said even though i have the first formula working on a set of "test" data on a test worksheet, when i try to alter the code to work off a table on the same worksheet it comes up with "#NUM!" . im stumped becuase i replace everything correctly outside of excel before pasting back into excel, or even go into formula edit and drag the boxes into the table i want to search and still get the same "#NUM!" error.

**NBVC** · Mar 3rd 2017

Re: VLOOKUP return multiple values

I don't think that Excel 2003 should be the problem. Are you sure you are confirming the formula with CTRL+SHIFT+ENTER? You should see { } brackets around the formula if done correctly?

If you change anything in the formula, you must re-confirm it as an array formula.

If still not working, please attach your workbook so we can see where you might be going wrong.

CaulfieldS · Mar 3rd 2017

Re: VLOOKUP return multiple values

Since the entire book contains some "privileged" information ive moved a couple pages i have been toying with to get a formula working to a new book with some raw data.

The attached workbook has 3 pages, 1st sheet "same page formula" has the data and formula working off the same page which will not be how i use the formula in the long run. the following page "lookup" contains the formula and the "keyword" then the last page "Data" contains the data table the formula should be searching for answers.

Note that on the lookup page the formula seems to work but the data i copied to the right being the data it should bring back shows the information it is grabbing is not accurate.

Thank you everyone for the help. It is much appreciated.

**NBVC** · Mar 3rd 2017

Re: VLOOKUP return multiple values

Since you are starting your formula one row lower that in your original sheet, you need to account for it in the "k" parameter of the SMALL() function:
[COLOR="#0000FF"]
=OFFSET(Data!$F$2,SMALL(IF(Data!$F$3:$F$5000=$A$3,ROW(Data!$F$3:$F$5000)-ROW(INDEX(Data!$F$3:$F$5000,1,1))+1),ROW()-3),COLUMN()-1)[/COLOR]

confirmed with CTRL+SHIFT+ENTER and copied down and to next column.

To hide the errors, you can use conditional formatting to change font to white.

VLOOKUP return multiple values

Participate now!

Share