Find more than 1 Value in a lookup

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
I am doing a vlookup which is working fine. But vlookup only returns the 1st occurrence of the lookup value. My array has more than one occurrence of the lookup value, and I need it to locate all such occurrences, not just the 1st one.

Eg. col A has John, Ted, Peter, John, Fred, John (going downwards)
col B has 7,9,4,8,5,2

In cell D1, I need Excel to find the 1st occurrence of John's name and return the value 7.

In cell E1 I need it to find the 2nd occurrence and return the value 8.


Cell F1 needs to end up with the value 2.

If there are more occurrences, they need to go in cells G1, H1 etc. This needs to happen for anything up to 20 times.

How can I get Excel to do this? Any help would be really appreciated!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Chip Pearson has a great srticle on this... but his site is down. Here is the gist:
test1.xls
IJKLM
10tomredtomred
11blue****orange
12orangeharryyellow
13#REF!pollygreen
14#REF!kelleyblue
15#REF!johnindigo
16#REF!janeviolet
17#REF!scottred
18#REF!bubbaorange
19#REF!buefordyellow
20jethrogreen
21tomblue
22****indigo
23harryviolet
24pollyred
25kelleyorange
26johnyellow
27janegreen
28scottblue
29bubbaindigo
30buefordviolet
31jethrored
32tomorange
Sheet2


where J10 is
Code:
=INDEX($M$10:$M$32,SMALL(IF($I$10=$L$10:$L$32,ROW($L$10:$L$32)-ROW($L$9),65537),ROW(1:1)))
confirmed with Ctrl+shift+enter rather than just enter.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
also, in J11, you can enter
Code:
=IF(COUNTA($J$10:J10)>=COUNTIF($L$10:$L$32,$I$10),"",INDEX($M$10:$M$32,SMALL(IF($I$10=$L$10:$L$32,ROW($L$10:$L$32)-ROW($L$9),65537),ROW(2:2))))
confirmed with CSE and copied down, to suppress the errors...
 

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
Ah! This is great. Thankyou! Let me ask another question a step further on...

1) Could your formulae be easily changed to give me the returned values across a row, rather than down a column?

2) The main question: In a workbook I have a couple of sheets. "Main" has the columns of original data (col A: John, Pete etc, col B: 6,5 etc, col C: math, science, english etc)

Then the other sheet "Summary" has col A a list of all possible names in some order (eg alphabetical), row 1 (across the top) has a list of the subjects maths, science etc and the cells in the table are the places I want to return the values.

So the formula in the Summary sheet cell B2 should use A2 (eg John) for its lookup value, but also take note of the subject name (eg Science) in row 1. Then it should go to Main sheet and look down col A until it finds the name of the person (eg John), then go across to col C and note the subject (eg Science).

It should then take note of the number in between (eg. 6) and back in sheet Summary in cell B2, return the number 6.

Cell C2 needs to do the identical thing, but use the subject name in C1.

The result needs to be in summary sheet, I get the names down column A and then in row 2 (eg John) going across it lists all his scores for whatever subjects he did.

At the end, of course, I could sum, average, whatever...

This is probably not explained very well but I'm confident it can be done, I'm just not sure what the magic key is. Again, I'd value your (more) help.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

The provisional answer is "yes"... but I am having a tough time visualizing your data structure.

In general, this method will work in the horizontal, simply make the ranges rows, not columns, then use the Column() function rather than the Row() function.

Here is Chip Pearson's article with more detail. if you would like to post a portion of your data table using the HTML Maker I can help you structure the formula properly...
 

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
OK. I couldn't get html maker to work (lots of continous errors I didn't understand) Also, I don't have any web server I can get an image onto. So I've done my best to reproduce what I mean below. Sorry, I know it's extremely unideal, but it may be a help...

Main worksheet
A B C D E
1 Name Score Subject
2 john 6 math
3 pete 8 math
4 bob 4 math
5 larry 7 science
6 ted 6 sose
7 john 9 science
8 bob 8 science
9 john 4 english
10 phil 6 sose
11

The names are in column A, scores in column B and subjects in column C. It's just a simple array of data.


Summary Worksheet

A B C D E F
1 math science english sose
2 bob
3 john
4 larry
5 pete
6 phil
7 ted
8

The names are listed in alphabetical order in column A beginning in cell A2. Then across row 1 (headers) are listed the different possible subjects, beginning in cell B1.

Now, I need to get Summary cell B2 to return the value 4 (which it gets from the Main w/s, after matching Bob and math), and C2 to return the value 8. These are both Bob's scores from the Main worksheet.

And that needs to happen for each of the other names also. Ultimately, the Summary cells B2:E7 would be filled with either a number, or blank, if no number matched.

Does this help? (I hope it does!)

Thanks
 

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70

ADVERTISEMENT

Oh... I think I may have just got around this fairly simply. I concatenated each cell of the name and subject on the Summary sheet. I also concatenated the name column with the subject column on the Main ws. Then did a basic vlookup using the summary concat value as the lookup value, and the array in the Main ws.

Again, this probably sounds confusing, but it seems to be working well and it's very simple. Amazing what a night's sleep will do. Thankyou for your help. If there are other (better?) ways to do this, though, I'm definitely still interested.

Cheers
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
If you are picking up the marks and referencing them to subjects by sequence (first result for "Dave" is "Math," second result is "Physics," etc), doesn't that create a problem if the subjects are not listed in the same order for all students, or if some students are not taking the same courses?

I would put the results into a Pivot Table, with Name as the row field, Subject as the column field, and either "Sum of Score" or "Average of Score" as the data field. If there is a risk of errors with more than one mark (either properly or improperly) entered, you could add the "Count of Score" as a second data field to ensure that all the right numbers of results have been entered - this would also be a (very weak) control over data accuracy. I often have to manipulate data to get it into the sort of flat file arrangement you are starting with, just because it is then so amenable to use in a Pivot Table.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Book3
ABCDEFGHIJ
1NameScoreSubjectSUBJECT
2   NameMATHSCIENCESOSEENGLISH
3john6mathjohn69 4
4pete8mathpete8   
5bob4mathbob48  
6larry7sciencelarry 7  
7ted6soseted  6 
8john9sciencephil  6 
9bob8science
10john4english
11phil6sose
12
Sheet1


Note the additional record A2:C2 whose cells are filled with:

=""

Then:

G3:

=INDEX($B$2:$B$11,MIN(IF($A$2:$A$11=$F3,IF($C$2:$C$11=G$2,ROW($A$2:$A$11)-ROW($A$2)+1))))

which is confirmed with control+shift+enter (not with enter) then copied across and down.

Alternatively, build a pivot table: See my next post...
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
[...]
Alternatively, build a pivot table: See my next post...
Book3
ABCDEFGHIJ
1NameScoreSubjectAverage of ScoreSubject
2john6mathNameenglishmathsciencesose
3pete8mathbob48
4bob4mathjohn469
5larry7sciencelarry7
6ted6sosepete8
7john9sciencephil6
8bob8scienceted6
9john4englishGrand Total4686
10phil6sose
11
Sheet1 (2)
 

Forum statistics

Threads
1,141,587
Messages
5,707,258
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top