Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are blanks being ignored by my MIN function? | Excel Worksheet Functions | |||
growth function with blanks | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) | |||
Excluding 0s and blanks from a LINEST function | Excel Worksheet Functions |