Macth 2 Column and Return a 3rd

[David Chin]

How do you search for a cell when you are trying to lookup two columns
and then return a third. For example, Column A is Last Name, Column B
is FIrst Name, Column C is Phone number. How do you retreive the
phone number when you have to match the Last name and First name?

 

[Craig Huggart]

David:

A cheap way to do this might be to combine the first and last name using
concatenation. Then you can use the resulting column in a VLOOKUP. Otherwise
you are going to probably want to use some IF statements.

Craig Huggart
Microsoft XP Master Instructor
(e-mail address removed)

 

[Dave Peterson]

I put my data in A1:C20.
I put the last name and first name in H1:I1

Then I used this formula:

=INDEX($C$1:$C$20,MATCH(H1&CHAR(1)&I1,$A$1:$A$20&CHAR(1)&$B1:$B$20,0))

But this is an array formula. Hit ctrl-shift-enter instead of just enter. If
you do it correctly, excel will wrap curly brackets {} around it. (don't type
them yourself).

 

[Alan Beban]

=INDEX($C$1:$C$20,MATCH(H1&I1,$A$1:$A$20&$B1:$B$20,0)) seems to work;
what is function of the CHAR(1)?

Alan Beban

 

[Dave Peterson]

Just in case:

Joan Derson
Jo Anderson

(Gawd, what an awful example.)

 

[Alan Beban]

Got the job done. So I guess anything works. E.g.,

=INDEX($C$1:$C$20,MATCH(H1&","&I1,$A$1:$A$20&","&$B1:$B$20,0))
=INDEX($C$1:$C$20,MATCH(H1&1&I1,$A$1:$A$20&1&$B1:$B$20,0))
=INDEX($C$1:$C$20,MATCH(H1&"whatever"&I1,$A$1:$A$20&"whatever"&$B1:$B$20,0))

Alan Beban