|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
September 20th, 2008, 11:57 AM
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
September 20th, 2008, 12:15 PM
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
September 20th, 2008, 12:16 PM
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
September 20th, 2008, 12:41 PM
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
September 20th, 2008, 12:42 PM
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
September 20th, 2008, 01:02 PM
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
September 20th, 2008, 02:33 PM
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
September 20th, 2008, 03:18 PM
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
September 20th, 2008, 04:05 PM
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
September 20th, 2008, 05:41 PM
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
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
