dismiss blanks in lookup
 

Hi, I have 3 columns
column A - 6 digit numbers that may repeat.(no blank cells)
column B - dates or blank (some blank cells)
column C - lookup formula
I want the cell in column C to look up a number in column A and return the
value in column B. If the cell in column B is empty, I want to find the next
match in column A and so on until it finds a date in Column B. Thank you in
advance.
--
Manuel

dismiss blanks in lookup
 

If your data is in A1:B100 and the value you want to lookup is in C1, try:

=INDEX(B1:B6,MATCH(1,(A1:A6=C1)*(B1:B6<""),0))

array entered with Cntrl+Shift+Enter (or you'll get an error).


"Manuel" wrote:

Hi, I have 3 columns
column A - 6 digit numbers that may repeat.(no blank cells)
column B - dates or blank (some blank cells)
column C - lookup formula
I want the cell in column C to look up a number in column A and return the
value in column B. If the cell in column B is empty, I want to find the next
match in column A and so on until it finds a date in Column B. Thank you in
advance.
--
Manuel

dismiss blanks in lookup
 

I don't think my question was clear. I want to lookup a number in Column A
and return the value in column B. if the cell in column B is blank then find
the next occurance of my lookup number in column A and return the value in
column B. if the cell in column B is blank again keep repeating untill you
find a a non blank cell.

Thank you again,
--
Manuel


"Manuel" wrote:

Hi, I have 3 columns
column A - 6 digit numbers that may repeat.(no blank cells)
column B - dates or blank (some blank cells)
column C - lookup formula
I want the cell in column C to look up a number in column A and return the
value in column B. If the cell in column B is empty, I want to find the next
match in column A and so on until it finds a date in Column B. Thank you in
advance.
--
Manuel

dismiss blanks in lookup
 

And that is exactly what it does when I tested it. Honestly, it doesn't
sound like you tried it - which I must point out is an important step in this
process. If that is incorrect and you did try it, please post more specific
details about what results you got, what you were expecting, and perhaps some
sample of your data.

Where cell C1 = 123, the formula posted returns 1/3/2001 using the data
below - which meets your needs as far as I can tell.

A B
123
456 1/1/2001
123
123 1/3/2001
456 1/4/2001


If you want to trap instances where there are no matches, you can wrap the
formula with an IF statement:

=IF(SUM((A1:A6=C1)*(B1:B6<"")),INDEX(B1:B6,MATCH( 1,(A1:A6=C1)*(B1:B6<""),0)),"No Matches")

Don't forget it should still be array entered with Cntrl+Shift+Enter (with
the cursor in the formula bar use Cntrl+Shift+Enter and not just Enter to
commit the formula). If done properly, XL will put braces { } around the
formula after it is entered. If not done properly, you will get an error or
potentially erronous results.


"Manuel" wrote:

I don't think my question was clear. I want to lookup a number in Column A
and return the value in column B. if the cell in column B is blank then find
the next occurance of my lookup number in column A and return the value in
column B. if the cell in column B is blank again keep repeating untill you
find a a non blank cell.

Thank you again,
--
Manuel


"Manuel" wrote:

Hi, I have 3 columns
column A - 6 digit numbers that may repeat.(no blank cells)
column B - dates or blank (some blank cells)
column C - lookup formula
I want the cell in column C to look up a number in column A and return the
value in column B. If the cell in column B is empty, I want to find the next
match in column A and so on until it finds a date in Column B. Thank you in
advance.
--
Manuel

dismiss blanks in lookup
 

I did try it and I couldn't get it to work. My fault not yours. I'm sorry I
didn't understand that Column C was supposed to be the value I was looking
for, now with that out of the way, it works fine.

Than you for helping a newbee
--
Manuel


"JMB" wrote:

And that is exactly what it does when I tested it. Honestly, it doesn't
sound like you tried it - which I must point out is an important step in this
process. If that is incorrect and you did try it, please post more specific
details about what results you got, what you were expecting, and perhaps some
sample of your data.

Where cell C1 = 123, the formula posted returns 1/3/2001 using the data
below - which meets your needs as far as I can tell.

A B
123
456 1/1/2001
123
123 1/3/2001
456 1/4/2001


If you want to trap instances where there are no matches, you can wrap the
formula with an IF statement:

=IF(SUM((A1:A6=C1)*(B1:B6<"")),INDEX(B1:B6,MATCH( 1,(A1:A6=C1)*(B1:B6<""),0)),"No Matches")

Don't forget it should still be array entered with Cntrl+Shift+Enter (with
the cursor in the formula bar use Cntrl+Shift+Enter and not just Enter to
commit the formula). If done properly, XL will put braces { } around the
formula after it is entered. If not done properly, you will get an error or
potentially erronous results.


"Manuel" wrote:

I don't think my question was clear. I want to lookup a number in Column A
and return the value in column B. if the cell in column B is blank then find
the next occurance of my lookup number in column A and return the value in
column B. if the cell in column B is blank again keep repeating untill you
find a a non blank cell.

Thank you again,
--
Manuel


"Manuel" wrote:

Hi, I have 3 columns
column A - 6 digit numbers that may repeat.(no blank cells)
column B - dates or blank (some blank cells)
column C - lookup formula
I want the cell in column C to look up a number in column A and return the
value in column B. If the cell in column B is empty, I want to find the next
match in column A and so on until it finds a date in Column B. Thank you in
advance.
--
Manuel

dismiss blanks in lookup
 

Thanks for posting back and no need for apology - we all make simple mistakes
(well, most of us do and I think the rest us do and I'm just not smart enough
to catch their mistakes <g). I apologize if I seem rude about asking if you
tried the suggestion - but I've learned it is a legitimate question (I'm not
sure why, but some folks assume something will not work and don't bother to
try it).




"Manuel" wrote:

I did try it and I couldn't get it to work. My fault not yours. I'm sorry I
didn't understand that Column C was supposed to be the value I was looking
for, now with that out of the way, it works fine.

Than you for helping a newbee
--
Manuel


"JMB" wrote:

And that is exactly what it does when I tested it. Honestly, it doesn't
sound like you tried it - which I must point out is an important step in this
process. If that is incorrect and you did try it, please post more specific
details about what results you got, what you were expecting, and perhaps some
sample of your data.

Where cell C1 = 123, the formula posted returns 1/3/2001 using the data
below - which meets your needs as far as I can tell.

A B
123
456 1/1/2001
123
123 1/3/2001
456 1/4/2001


If you want to trap instances where there are no matches, you can wrap the
formula with an IF statement:

=IF(SUM((A1:A6=C1)*(B1:B6<"")),INDEX(B1:B6,MATCH( 1,(A1:A6=C1)*(B1:B6<""),0)),"No Matches")

Don't forget it should still be array entered with Cntrl+Shift+Enter (with
the cursor in the formula bar use Cntrl+Shift+Enter and not just Enter to
commit the formula). If done properly, XL will put braces { } around the
formula after it is entered. If not done properly, you will get an error or
potentially erronous results.


"Manuel" wrote:

I don't think my question was clear. I want to lookup a number in Column A
and return the value in column B. if the cell in column B is blank then find
the next occurance of my lookup number in column A and return the value in
column B. if the cell in column B is blank again keep repeating untill you
find a a non blank cell.

Thank you again,
--
Manuel


"Manuel" wrote:

Hi, I have 3 columns
column A - 6 digit numbers that may repeat.(no blank cells)
column B - dates or blank (some blank cells)
column C - lookup formula
I want the cell in column C to look up a number in column A and return the
value in column B. If the cell in column B is empty, I want to find the next
match in column A and so on until it finds a date in Column B. Thank you in
advance.
--
Manuel