Indexing beyond blanks

L

Legacy 5596

Guest
Hello I'm using index & match to locate a match in one column & return the contents of another:

=IF(ISERROR(INDEX(database!$B$2:$B$2000, MATCH(A1, database!$A$2:$A$2000, FALSE),1)),"Not Found", INDEX(database!$B$2:$B$2000, MATCH(A1, database!$A$2:$A$2000, FALSE),1))

This works fine & just to break it down it is interacting with 2 sheets.

A1 is the on the source sheet & is the criteria to be matched on database!$A$2:$A$2000 -- If its matched, then return the contents of database!$B$2:$B$2000 of the corresponding row of the first matched row of database!$A$2:$A$2000

Here's the problem, Suppose database!$A$2:$A$2000 is a list of names & database!$B$2:$B$2000 is a list of phone numbers, BUT suppose on first match doesn't have a phone number listed. I want to go to the NEXT match that DOES have a phone number & return that.

Help is appreciated. Thanks
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
A little ugly but see if this works for you:

=IF(ISNA(MATCH(A1,database!$A$2:$A$2000,FALSE)),"not found",IF(INDEX(database!$B$2:$B$2000,MATCH(A1,database!$A$2:$A$2000,FALSE),1)="",INDEX(database!$B$2:$B$2000,SMALL(IF(database!$A$2:$A$2000=A1,ROW($A$2:$A$2000)-ROW($A$2)+1),2)),INDEX(database!$B$2:$B$2000,MATCH(A1,database!$A$2:$A$2000,FALSE),1)))

formula must be confirmed with CTRL+SHIFT+ENTER not just Enter. You will see {} brackets if the formula is confirmed correctly.
 
L

Legacy 5596

Guest
Worked

That code worked like a charm until a broke it :) -- purposely. The problem now is it will find the NEXT in the list if the previous was blank, but if the number may not always be the NEXt, it could be blank for many rows before the number is listed. That excellent code you gave me returns a 0 unless the number is NEXT :( Anymore suggestions?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hello I'm using index & match to locate a match in one column & return the contents of another:

=IF(ISERROR(INDEX(database!$B$2:$B$2000, MATCH(A1, database!$A$2:$A$2000, FALSE),1)),"Not Found", INDEX(database!$B$2:$B$2000, MATCH(A1, database!$A$2:$A$2000, FALSE),1))

This works fine & just to break it down it is interacting with 2 sheets.

A1 is the on the source sheet & is the criteria to be matched on database!$A$2:$A$2000 -- If its matched, then return the contents of database!$B$2:$B$2000 of the corresponding row of the first matched row of database!$A$2:$A$2000

Here's the problem, Suppose database!$A$2:$A$2000 is a list of names & database!$B$2:$B$2000 is a list of phone numbers, BUT suppose on first match doesn't have a phone number listed. I want to go to the NEXT match that DOES have a phone number & return that.

Help is appreciated. Thanks

=INDEX(database!$B$2:$B$2000,MIN(IF(database!$A$2:$A$2000=A1,IF(database!$B$2:$B$2000="","",ROW(database!$B$2:$B$2000)-ROW(database!$B$2)+1))))

which must be confirmed with control+shift+enter.
 
L

Legacy 5596

Guest

ADVERTISEMENT

So smart

man you guys are soooo smart -- that works like a charm. I even tried to break it by skipping some rows & placing the phone number/name after entries of other names & it withstood the test. Thanks again all!!! MrExcel users saved my life...well made my job more secure at least :)
 
L

Legacy 5596

Guest
ah it did break afterall & here's how. Suppose I had 2 sets of names ABC Company & XYZ Company. ABC Company listed in col A1-A4 on the database sheet but ABC Company's phone number listed on B3.

Your code did great listing the number. The problem comes in here. If XYZ Company which is listed A5-A6 has no number listed in either B5-B6 the code automatically is using ABC Company's number for XYZ Company. Does this make sense?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Can you post examples using Colo's HTML maker (see my signature for instructions) showing this issue?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
ah it did break afterall & here's how. Suppose I had 2 sets of names ABC Company & XYZ Company. ABC Company listed in col A1-A4 on the database sheet but ABC Company's phone number listed on B3.

Your code did great listing the number. The problem comes in here. If XYZ Company which is listed A5-A6 has no number listed in either B5-B6 the code automatically is using ABC Company's number for XYZ Company. Does this make sense?

This will a bit technical...

Insert a new record at the beginning of data on database, which is now in A2:B2001.

Enter Default in A2 and Not Found in B2.

Now invoke:

=INDEX(database!$B$2:$B$2001,MIN(IF(database!$A$2:$A$2001=A1,IF(database!$B$2:$B$2001="","",ROW(database!$B$2:$B$2001)-ROW(database!$B$2)+1))))

Still to be confirmed with control+shift+enter.
 
L

Legacy 5596

Guest
You did it again

Aaldin you did it again, maybe you should negotiate the Middle-East peace :) -- thanks & to you too smurfy. I had tried to use that html paster in the past but it didn't work right on my computer (can't recall what went wrong) -- I'm going to try to break this one more time :LOL:
 
L

Legacy 5596

Guest
nope couldn't break it, thought maybe if people ran a filter on database they might break it but it still worked. Perhaps if they sorted it, but I'll just not allow sort :)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,148
Members
410,666
Latest member
Al3cs
Top