A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Match formula to match values in multiple columns



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2010, 12:25 PM posted to microsoft.public.excel.misc
K[_4_]
external usenet poster
 
Posts: 25
Default Match formula to match values in multiple columns

Hi all, does any friend know that how can I make below formula work
MATCH(A2,$K$2:$M$30,0)
  #2  
Old April 21st, 2010, 01:07 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Match formula to match values in multiple columns

Maybe you can use =countif()

=if(countif($k$2:$M$30,a2)=0,"not there","it's there at least once")

K wrote:

Hi all, does any friend know that how can I make below formula work
MATCH(A2,$K$2:$M$30,0)


--

Dave Peterson
  #3  
Old April 21st, 2010, 01:08 PM posted to microsoft.public.excel.misc
eduardo
external usenet poster
 
Posts: 2,131
Default Match formula to match values in multiple columns

Hi,
If you provide an example of your data and the results you are looking for,
we can help

"K" wrote:

Hi all, does any friend know that how can I make below formula work
MATCH(A2,$K$2:$M$30,0)
.

  #4  
Old April 21st, 2010, 01:36 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Match formula to match values in multiple columns

Check your other post. You dont need to multi-post


"K" wrote:

Hi all, does any friend know that how can I make below formula work
MATCH(A2,$K$2:$M$30,0)
.

  #5  
Old April 21st, 2010, 02:01 PM posted to microsoft.public.excel.misc
K[_4_]
external usenet poster
 
Posts: 25
Default Match formula to match values in multiple columns

Thanks lot Dave Peterson. Your formula works. What i was trying to
achive that i got data in three columns like see below

K L M.....col
XX YY GG
SS TT NN
RR VV AA
etc...

then i have data in column A like see below

A....col
XX
DD
SS

I needed some formula in column B to match column A values in columns
K, L and M to come back with result like see below

A B....col
XX Match
DD Dont Match
SS Match

sorry i didnt explain my question clearly as i was trying to keep it
short. The only thing i am trying to workout that how can i achive
same thing with macro. Like if i click button and column B should get
filled automatically. It will much appricated if any friend can help
  #6  
Old April 21st, 2010, 04:53 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Match formula to match values in multiple columns

I'd just insert the formula into the macro:

Dim wks as worksheet
dim LastRow as long

set wks = worksheets("Sheet1")

with wks
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("B2:B" & lastrow)
.formula = "=if(countif($k$2:$M$30,a2)=0,""no match"",""match"")"
.value = .value 'convert formulas to values???
end with
end with

Notice that the double quotes in the formula string are doubled. Something to
watch out for if/when you change that formula.




K wrote:

Thanks lot Dave Peterson. Your formula works. What i was trying to
achive that i got data in three columns like see below

K L M.....col
XX YY GG
SS TT NN
RR VV AA
etc...

then i have data in column A like see below

A....col
XX
DD
SS

I needed some formula in column B to match column A values in columns
K, L and M to come back with result like see below

A B....col
XX Match
DD Dont Match
SS Match

sorry i didnt explain my question clearly as i was trying to keep it
short. The only thing i am trying to workout that how can i achive
same thing with macro. Like if i click button and column B should get
filled automatically. It will much appricated if any friend can help


--

Dave Peterson
  #7  
Old April 22nd, 2010, 10:22 AM posted to microsoft.public.excel.misc
K[_4_]
external usenet poster
 
Posts: 25
Default Match formula to match values in multiple columns

On Apr 21, 4:53*pm, Dave Peterson wrote:
I'd just insert the formula into the macro:

Dim wks as worksheet
dim LastRow as long

set wks = worksheets("Sheet1")

with wks
* lastrow = .cells(.rows.count,"A").end(xlup).row
* with .range("B2:B" & lastrow)
* * .formula = "=if(countif($k$2:$M$30,a2)=0,""no match"",""match"")"
* * .value = .value 'convert formulas to values???
* end with
end with

Notice that the double quotes in the formula string are doubled. *Something to
watch out for if/when you change that formula.





K wrote:

Thanks lot Dave Peterson. *Your formula works. *What i was trying to
achive that i got data in three columns like see below


K * *L * M.....col
XX *YY GG
SS TT NN
RR VV AA
etc...


then i have data in column A like see below


A....col
XX
DD
SS


I needed some formula in column B to match column A values in columns
K, L and M to come back with result like see below


A * * *B....col
XX * *Match
DD * Dont Match
SS * Match


sorry i didnt explain my question clearly as i was trying to keep it
short. *The only thing i am trying to workout that how can i achive
same thing with macro. Like if i click button and column B should get
filled automatically. *It will much appricated if any friend can help


--

Dave Peterson- Hide quoted text -

- Show quoted text -


thanks lot dave
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.