MATCH() Function and Blanks
 

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

Hi,

Try this

SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gary''s Student" wrote in message
...
I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

On Sat, 20 Sep 2008 03:57:00 -0700, Gary''s Student
wrote:

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??

Try the following formula.

=MATCH("x"&B1,"x"&A1:A5,0)

Note that this is an array formula that has to be entered with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

MATCH() Function and Blanks
 

Thanks!
--
Gary''s Student - gsnu200805


"Ashish Mathur" wrote:

Hi,

Try this

SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gary''s Student" wrote in message
...
I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

Thanks!
Also thank you for your help several weeks ago.
--
Gary''s Student - gsnu200805


"Lars-Ã…ke Aspelin" wrote:

On Sat, 20 Sep 2008 03:57:00 -0700, Gary''s Student
wrote:

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??

Try the following formula.

=MATCH("x"&B1,"x"&A1:A5,0)

Note that this is an array formula that has to be entered with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Ã…ke

MATCH() Function and Blanks
 

You are welcome

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gary''s Student" wrote in message
...
Thanks!
--
Gary''s Student - gsnu200805


"Ashish Mathur" wrote:

Hi,

Try this

SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gary''s Student" wrote in
message
...
I have a column of values and am trying to identify where values appear
in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in
this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

=MATCH(TRUE,INDEX(A1:A5=B1,),)


"Gary''s Student" wrote:

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

Thanks....very nice solution
--
Gary''s Student - gsnu200805


"Teethless mama" wrote:

=MATCH(TRUE,INDEX(A1:A5=B1,),)


"Gary''s Student" wrote:

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

You're Welcome!



"Gary''s Student" wrote:

Thanks....very nice solution
--
Gary''s Student - gsnu200805


"Teethless mama" wrote:

=MATCH(TRUE,INDEX(A1:A5=B1,),)


"Gary''s Student" wrote:

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

one more option, array entered

=MATCH(C2&"",A1:A4&"",0)


"Gary''s Student" wrote:

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


--


Regards,


Peo Sjoblom

"Ashish Mathur" wrote in message
...
You are welcome

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gary''s Student" wrote in
message ...
Thanks!
--
Gary''s Student - gsnu200805


"Ashish Mathur" wrote:

Hi,

Try this

SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gary''s Student" wrote in
message
...
I have a column of values and am trying to identify where values
appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in
this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

On Sat, 20 Sep 2008 09:51:45 -0700, "Peo Sjoblom"
wrote:

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


I think it will work if you e.g. change A1:A5 to A6:A10 as long as you
do NOT change the 1:5 in the ROW().

Lars-Åke

MATCH() Function and Blanks
 

Thanks!
--
Gary''s Student - gsnu200805


"JMB" wrote:

one more option, array entered

=MATCH(C2&"",A1:A4&"",0)


"Gary''s Student" wrote:

I have a column of values and am trying to identify where values appear in
the column. For example, in A1 thru A5:

cow
mouse

pig
horse


Note A3 is blank. If I put "pig" in B1 then the formula

=MATCH(B1,A1:A5,0)

correctly returns 4

If I leave B1 blank, the formula returns #N/A rather than 3.
I need the result to be 3.

I can make a UDF to give the correct result, but I can't use VBA in this
application.

So can I find values in a list even if the value is a blank??
--
Gary''s Student - gsnu2007xx

MATCH() Function and Blanks
 

It will count from row 1 so if you have values from A6 to A10 and if B1 is
blank and if
A7 is blank all other formulas (yours included) will return 2 since it is
the 2nd cell in the range whereas this will return 7 which is correct if you
count from row 1 but you would need to offset it by the 5 cells above A6 to
get the same result as the other formulas

--


Regards,


Peo Sjoblom

"Lars-Åke Aspelin" wrote in message
...
On Sat, 20 Sep 2008 09:51:45 -0700, "Peo Sjoblom"
wrote:

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


I think it will work if you e.g. change A1:A5 to A6:A10 as long as you
do NOT change the 1:5 in the ROW().

Lars-Åke

MATCH() Function and Blanks
 

I think the issue here Peo, is *actual* location as opposed to *relative*
location.

Since Gary's OP mentioned Match(), which does return relative, I believe
everyone is thinking *relative*.

=SUMPRODUCT((A6:A10=B1)*(ROW(1:5)))

will return "3", when the data in A6:A10 is exactly the same as the data in
the OP, which would be the return you would expect from a formula using
Match().

I think you and Lars and Ashish are talking apples and oranges.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
It will count from row 1 so if you have values from A6 to A10 and if B1 is
blank and if
A7 is blank all other formulas (yours included) will return 2 since it is
the 2nd cell in the range whereas this will return 7 which is correct if
you
count from row 1 but you would need to offset it by the 5 cells above A6
to
get the same result as the other formulas

--


Regards,


Peo Sjoblom

"Lars-Åke Aspelin" wrote in message
...
On Sat, 20 Sep 2008 09:51:45 -0700, "Peo Sjoblom"
wrote:

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


I think it will work if you e.g. change A1:A5 to A6:A10 as long as you
do NOT change the 1:5 in the ROW().

Lars-Åke

MATCH() Function and Blanks
 

True but if someone sees the formula

=SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))


I don't think it is far fetched to think that if one change A1:A5 to A6:A10
one would also change
ROW(1:5) to ROW(6:10)

and then the return would be absolute. I just think it is a less good way
than using MATCH

even if that includes array entering (except TM's)

--


Regards,


Peo Sjoblom

"Ragdyer" wrote in message
...
I think the issue here Peo, is *actual* location as opposed to *relative*
location.

Since Gary's OP mentioned Match(), which does return relative, I believe
everyone is thinking *relative*.

=SUMPRODUCT((A6:A10=B1)*(ROW(1:5)))

will return "3", when the data in A6:A10 is exactly the same as the data
in
the OP, which would be the return you would expect from a formula using
Match().

I think you and Lars and Ashish are talking apples and oranges.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
It will count from row 1 so if you have values from A6 to A10 and if B1
is
blank and if
A7 is blank all other formulas (yours included) will return 2 since it is
the 2nd cell in the range whereas this will return 7 which is correct if
you
count from row 1 but you would need to offset it by the 5 cells above A6
to
get the same result as the other formulas

--


Regards,


Peo Sjoblom

"Lars-Åke Aspelin" wrote in message
...
On Sat, 20 Sep 2008 09:51:45 -0700, "Peo Sjoblom"
wrote:

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


I think it will work if you e.g. change A1:A5 to A6:A10 as long as you
do NOT change the 1:5 in the ROW().

Lars-Åke

MATCH() Function and Blanks
 

My 2 cents...

Assuming there is only one empty cell.

I would use the array formula:

=MATCH(TRUE,A7:A11=B1,0)

Using other methods you'd have to calculate the offset for a relative
result:

=SUMPRODUCT((A7:A11=B1)*(ROW(A7:A11)-ROW(A7)+1))

=SUMPRODUCT((A7:A11=B1)*(ROW(A7:A11)-MIN(ROW(A7:A11))+1))

=LOOKUP(2,1/(A7:A11=B1),ROW(A7:A11)-ROW(A7)+1)

=LOOKUP(2,1/(A7:A11=B1),ROW(A7:A11)-MIN(ROW(A7:A11))+1)

If the data was numeric then you'd need something more robust.

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
True but if someone sees the formula

=SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))


I don't think it is far fetched to think that if one change A1:A5 to
A6:A10 one would also change
ROW(1:5) to ROW(6:10)

and then the return would be absolute. I just think it is a less good way
than using MATCH

even if that includes array entering (except TM's)

--


Regards,


Peo Sjoblom

"Ragdyer" wrote in message
...
I think the issue here Peo, is *actual* location as opposed to *relative*
location.

Since Gary's OP mentioned Match(), which does return relative, I believe
everyone is thinking *relative*.

=SUMPRODUCT((A6:A10=B1)*(ROW(1:5)))

will return "3", when the data in A6:A10 is exactly the same as the data
in
the OP, which would be the return you would expect from a formula using
Match().

I think you and Lars and Ashish are talking apples and oranges.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
It will count from row 1 so if you have values from A6 to A10 and if B1
is
blank and if
A7 is blank all other formulas (yours included) will return 2 since it
is
the 2nd cell in the range whereas this will return 7 which is correct if
you
count from row 1 but you would need to offset it by the 5 cells above A6
to
get the same result as the other formulas

--


Regards,


Peo Sjoblom

"Lars-Åke Aspelin" wrote in message
...
On Sat, 20 Sep 2008 09:51:45 -0700, "Peo Sjoblom"
wrote:

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


I think it will work if you e.g. change A1:A5 to A6:A10 as long as you
do NOT change the 1:5 in the ROW().

Lars-Åke

MATCH() Function and Blanks
 

Thank you Bif.

Either relative or absolute will work for me as I can OFFSET() from either
A1 or the table corner.

It just that after all this time, I never realized that MATCH() would have a
problem with blanks. My first instinct was to run and hide behind VBA.
However you and the others have taught me that UDFs are rarely needed for
something like this.

I should be thankful that I have not been required to make MATCH() work with
#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Once again, thank you (and the others) for taking the time to help me.
--
Gary''s Student - gsnu200805


"T. Valko" wrote:

My 2 cents...

Assuming there is only one empty cell.

I would use the array formula:

=MATCH(TRUE,A7:A11=B1,0)

Using other methods you'd have to calculate the offset for a relative
result:

=SUMPRODUCT((A7:A11=B1)*(ROW(A7:A11)-ROW(A7)+1))

=SUMPRODUCT((A7:A11=B1)*(ROW(A7:A11)-MIN(ROW(A7:A11))+1))

=LOOKUP(2,1/(A7:A11=B1),ROW(A7:A11)-ROW(A7)+1)

=LOOKUP(2,1/(A7:A11=B1),ROW(A7:A11)-MIN(ROW(A7:A11))+1)

If the data was numeric then you'd need something more robust.

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
True but if someone sees the formula

=SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))


I don't think it is far fetched to think that if one change A1:A5 to
A6:A10 one would also change
ROW(1:5) to ROW(6:10)

and then the return would be absolute. I just think it is a less good way
than using MATCH

even if that includes array entering (except TM's)

--


Regards,


Peo Sjoblom

"Ragdyer" wrote in message
...
I think the issue here Peo, is *actual* location as opposed to *relative*
location.

Since Gary's OP mentioned Match(), which does return relative, I believe
everyone is thinking *relative*.

=SUMPRODUCT((A6:A10=B1)*(ROW(1:5)))

will return "3", when the data in A6:A10 is exactly the same as the data
in
the OP, which would be the return you would expect from a formula using
Match().

I think you and Lars and Ashish are talking apples and oranges.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
It will count from row 1 so if you have values from A6 to A10 and if B1
is
blank and if
A7 is blank all other formulas (yours included) will return 2 since it
is
the 2nd cell in the range whereas this will return 7 which is correct if
you
count from row 1 but you would need to offset it by the 5 cells above A6
to
get the same result as the other formulas

--


Regards,


Peo Sjoblom

"Lars-Ã…ke Aspelin" wrote in message
...
On Sat, 20 Sep 2008 09:51:45 -0700, "Peo Sjoblom"
wrote:

No offense but that formula is not generic and it will return
an incorrect value if for instance the list is in A6:A10
Not only that but it will always count from row 1!


I think it will work if you e.g. change A1:A5 to A6:A10 as long as you
do NOT change the 1:5 in the ROW().

Lars-Ã…ke