Multiple Lookup Values

[welshwiz9]

Hi

I've made an example of my issue and tried to keep it as simple as I can. Let's say there has been exams and children get their grades (in Sheet 2):

Name_____Subject____Grade____Comment
Tom______Geography__G_______Poor
Tom______Art________A_______Great
Tom______English_____B_______Good

I want to change these automatically with lookups into (Sheet 1):

Name ____Art____ English____Geography
Tom _____ A ____ B________ G

But my lookups only pick up the first "Tom" meaning it makes (Sheet 1):

Name____ Art______ English______Geography
Tom__________________________ G

It therefore ignores the second two "Tom"'s

Code I use is: =IF(VLOOKUP($A$2,Sheet2!$A$1:$D$4,2,FALSE)=D$1,VLOOKUP($A$2,Sheet2!$A$1:$D$4,3,FALSE),"")

Any help here? Would be superb if you could help!

Thanks, G

 

[SpillerBD]

How can you distinguish between the "Tom"s? How could Excel? No Last name or other identifying field available?

 

[Marcelo Branco]

Welcome to Mr Excel

Say you have something like this in Sheet2

	A​	B​	C​	D​
1​	Name​	Subject​	Grade​	Comment​
2​	Tom​	Geography​	G​	Poor​
3​	Tom​	Art​	A​	Great​
4​	Tom​	English​	B​	Good​
5​	Mike​	Geography​	A​	Great​
6​	Mike​	English​	B​	Good​

Sheet1

	A​	B​	C​	D​
1​	Name​	Art​	English​	Geography​
2​	Tom​	A​	B​	G​
3​	Mike​	Not Available​	B​	A​

Array formula in B2 copied across and down
=IFERROR(INDEX(Sheet2!$C$2:$C$100,MATCH(1,IF(Sheet2!$A$2:$A$100=$A2,IF(Sheet2!$B$2:$B$100=B$1,1)),0)),"Not Available")

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[welshwiz9]

Brilliant!

Thankyou so much! Never used that cntrl shift enter thing, what exactly does that do to make it work?

 

[Marcelo Branco]

Brilliant!

Thankyou so much! Never used that cntrl shift enter thing, what exactly does that do to make it work?

Take a look at
Array Formulas

M.