Indexing beyond blanks

[Legacy 5596]

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

 

[NBVC]

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.

 

[Legacy 5596]

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]

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.

 

[Legacy 5596]

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

 

[Legacy 5596]

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]

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

 

[Aladin Akyurek]

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.

 

[Legacy 5596]

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

 

[Legacy 5596]

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