index / match function

[Guest]

Criterion
SIN 212475929
Student # =INDEX(Student,MATCH($B$2,SIN,0))
Last Name =INDEX(Last_Name,MATCH($B$2,SIN,0))
First Name =INDEX(First_Name,MATCH($B$2,SIN,0))

I have a table on another worksheet where the user will enter in the SIN #
and the other information is extracted from it. I need to lookup this
information using ONLY ONE formula. As you can see here I have 3 different
ones.

Any ideas?
Lisa

 

[Peo Sjoblom]

This entered with ctrl + shift & enter should give you all 3 items

=INDEX(Last_Name&" "&First_Name&" "&Student,MATCH($B$2,SIN,0))

you can switch places between the ranges

 

[Guest]

Thanks,
When I enter this they appear all in the same cell?? How can I display the
information in different cells?

 

[Peo Sjoblom]

If you use a name for all three ranges, let's call it MyTable, then select 3
cells across and use this formula (also array entered)

=INDEX(MyTable,MATCH($B$2,SIN,0),COLUMN(A:C))

COLUMN(A:C) has noting to do with those particular columns, you can also use

=INDEX(MyTable,MATCH($B$2,SIN,0),{1,2,3})