=Index Problem

[Guest]

Hi,

I have a spreadsheet with the following Columns:
A = Last Names
B= First Names
C = Extentions
E,F,G are blank
H = Last Names (only a selection of colum A)
I = First Names (the corresponding first names)
J = Cell Phone Numbers

I have used the following forumla

=INDEX($A$1:$J$365,MATCH($A6,$H$1:$H$279),10)

I would like it to look in Column H and if it finds the same last name as in
Column A put the corresponding cell phone number from Column J into Column L

This works so far except if it cannot find a last name in Column H it puts
the cell phone number from the line previous until it findsa matching last
name again.

I would like it just to stay blank if it can't find the matching last name.
What can I change in my formula to achieve this?

Thanks

 

[Guest]

Hi Nikki,

=IF(ISNA(INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10)),"",INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10))

The above is a tested formula. You need to edit with your ranges.

Also it is actually one line but does not fit here.

Note the zero parameter at end of match formula. See help for what it does.

Regards,

OssieMac

 

[Harlan Grove]

=IF(ISNA(INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10)),"",
INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10))

....

You could shorten the existence check considerably.

=IF(COUNTIF($H$1:$H$30,$A1),
INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"")

or, possibly faster recalculating,

=IF(COUNT(MATCH($A1,$H$1:$H$30,0)),
INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"")

 

[Guest]

Hi,

Thanks they all work.

Now I have come across the problem that some people have the same last name
so I need to change the formula so that it checks First and Last names
(Columns A, B with Columns H, I)

Any ideas?

Regards

Nikki

 

[Guest]

Hi,

Thanks they all work.

Now I have come across the problem that some people have the same last name
so I need to change the formula so that it checks First and Last names
(Columns A, B with Columns H, I)

Any ideas?

Regards

Nikki

 

[Peo Sjoblom]

Change the match part

INDEX($A$1:$J$30,MATCH($A1,$H$1:$H$30,0),10),"")




MATCH(1,(H1:H30=A1)*(I1:I30=B1),0)

and you need to enter the formula with ctrl + shift & enter


--

Regards,

Peo Sjoblom