Match formula to match values in multiple columns

[K]

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

 

[Dianne]

Hi all, does any friend know that how can I make below formula work

MATCH(A2,$K$2:$M$30,0)

K,

You will need to give more information or attach an example so that w
can see what problem you are having.

Dianne

 

[Dave Peterson]

Maybe you can use =countif()

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

 

[Eduardo]

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

 

[Jacob Skaria]

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

 

[K]

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]

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]

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.










--

Dave Peterson- Hide quoted text -

- Show quoted text -

thanks lot dave