Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=Index Problem
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=Index Problem
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),1 0)) 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 "Nikki" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=Index Problem
"OssieMac" wrote...
.... =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),"") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
=Index Problem
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 "Harlan Grove" wrote: "OssieMac" wrote... .... =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),"") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
=Index Problem
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 "Harlan Grove" wrote: "OssieMac" wrote... .... =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),"") |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
=Index Problem
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 "Nikki" wrote in message ... 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 "Harlan Grove" wrote: "OssieMac" wrote... .... =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),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index formula problem | Excel Worksheet Functions | |||
Index/Match problem | Excel Worksheet Functions | |||
INDEX PROBLEM...I THINK | Excel Worksheet Functions | |||
INDEX / MATCH problem | Excel Worksheet Functions | |||
INDEX problem | Excel Worksheet Functions |