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
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
Try this
SUMPRODUCT((A1:A5=B1)*(ROW(1:5)))
--
Regards,
Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Gary''s Student" <GarysS...@discussions.microsoft.com> wrote in message
news:F05D14C6-9009-4BC3...@microsoft.com...
--
Regards,
Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Gary''s Student" <GarysS...@discussions.microsoft.com> wrote in message
news:05C19ED7-2722-4638...@microsoft.com...
=MATCH(C2&"",A1:A4&"",0)
--
Regards,
Peo Sjoblom
"Ashish Mathur" <mathur...@hotmail.com> wrote in message
news:E8ED23B9-34F3-4838...@microsoft.com...
>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
--
Regards,
Peo Sjoblom
"Lars-Åke Aspelin" <lar...@REMOOOVE.telia.com> wrote in message
news:6rbad4908kunjvri5...@4ax.com...
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" <peo.s...@mvps.org> wrote in message
news:%23LBAi30...@TK2MSFTNGP02.phx.gbl...
=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" <Rag...@cutoutmsn.com> wrote in message
news:ugftxT1G...@TK2MSFTNGP06.phx.gbl...
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" <peo.s...@mvps.org> wrote in message
news:e78AL01G...@TK2MSFTNGP04.phx.gbl...
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