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


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Name​

[/td][td]

Subject​

[/td][td]

Grade​

[/td][td]

Comment​

[/td][/tr]


[tr][td]

2​

[/td][td]

Tom​

[/td][td]

Geography​

[/td][td]

G​

[/td][td]

Poor​

[/td][/tr]


[tr][td]

3​

[/td][td]

Tom​

[/td][td]

Art​

[/td][td]

A​

[/td][td]

Great​

[/td][/tr]


[tr][td]

4​

[/td][td]

Tom​

[/td][td]

English​

[/td][td]

B​

[/td][td]

Good​

[/td][/tr]


[tr][td]

5​

[/td][td]

Mike​

[/td][td]

Geography​

[/td][td]

A​

[/td][td]

Great​

[/td][/tr]


[tr][td]

6​

[/td][td]

Mike​

[/td][td]

English​

[/td][td]

B​

[/td][td]

Good​

[/td][/tr]
[/table]

Sheet1


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Name​

[/td][td]

Art​

[/td][td]

English​

[/td][td]

Geography​

[/td][/tr]


[tr][td]

2​

[/td][td]

Tom​

[/td][td]

A​

[/td][td]

B​

[/td][td]

G​

[/td][/tr]


[tr][td]

3​

[/td][td]

Mike​

[/td][td]

Not Available​

[/td][td]

B​

[/td][td]

A​

[/td][/tr]
[/table]

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.