Find more than 1 Value in a lookup

[rline101]

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!

 

[hatman]

Chip Pearson has a great srticle on this... but his site is down. Here is the gist:

test1.xls
	I	J	K	L	M
10	tom	red		tom	red
11		blue		****	orange
12		orange		harry	yellow
13		#REF!		polly	green
14		#REF!		kelley	blue
15		#REF!		john	indigo
16		#REF!		jane	violet
17		#REF!		scott	red
18		#REF!		bubba	orange
19		#REF!		bueford	yellow
20				jethro	green
21				tom	blue
22				****	indigo
23				harry	violet
24				polly	red
25				kelley	orange
26				john	yellow
27				jane	green
28				scott	blue
29				bubba	indigo
30				bueford	violet
31				jethro	red
32				tom	orange
Sheet2

where J10 is

=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]

also, in J11, you can enter

=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]

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]

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]

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]

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]

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]

Book3
	A	B	C	D	E	F	G	H	I	J
1	Name	Score	Subject				SUBJECT
2						Name	MATH	SCIENCE	SOSE	ENGLISH
3	john	6	math			john	6	9		4
4	pete	8	math			pete	8
5	bob	4	math			bob	4	8
6	larry	7	science			larry		7
7	ted	6	sose			ted			6
8	john	9	science			phil			6
9	bob	8	science
10	john	4	english
11	phil	6	sose
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]

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

Book3
	A	B	C	D	E	F	G	H	I	J
1	Name	Score	Subject			Average of Score	Subject
2	john	6	math			Name	english	math	science	sose
3	pete	8	math			bob		4	8
4	bob	4	math			john	4	6	9
5	larry	7	science			larry			7
6	ted	6	sose			pete		8
7	john	9	science			phil				6
8	bob	8	science			ted				6
9	john	4	english			Grand Total	4	6	8	6
10	phil	6	sose
11
Sheet1 (2)