# Match formula

I need to match data from one column with another. If the numbers match it needs to say "deployed", and if not (not deployed") If a value in A1 = value in B25, then "deployed", "not deployed"
###### Who is Participating?

IT ManagerCommented:
you might want to use ISERROR instead:

=IF(ISERROR(MATCH(A813,Reference!A:A,0)),"not deployed","deployed")

The logic of this is reversed to normal:
If Match generates an error, then show Not Deployed, else show Deployed.

I also removed your >0, as I wasn't sure what it was adding...
0

Commented:
=IF(A1=B25, "Deployed", "Not Deployed")
0

Author Commented:
that was just an example...numbers in column A are in one order, and numbers in column B (totally different file) are in a different order.  The goal is to find a value from A1 in column B, once they are found then it's deployed, if not "not deployed"
0

Author Commented:
Pls refer to the attached file. Thx!
Matching-values.xlsx
0

CINCommented:
Hi Ladkisson, i think the match function could be helpful, in the example you mention it would be like this:

=IF(MATCH(A1,B1:B100,0)>0,"Deployed","Not deployed")

Heres the link to the documentation of the function:
http://office.microsoft.com/en-us/excel-help/match-HP005209168.aspx?CTT=5&origin=HP005204211

Hope this helps
Ruben
0

Author Commented:
Yes! this is even in the the title of my question. One little thing, the section B1-B100 does not have some of the numbers that are in column A - so your formula instead of saying "not depoyed" says "N/A"

how can this be fixed?
0

CINCommented:
You need to change the range of the column you are looking.
It nned to be from you first value to the last, say you have values from B2 to B2000, you put \$B\$2:\$B\$2000 (I forgot tu put the \$ symbol in the first formula, this prevents the range to change when you copy the formula)

Ruben
0

Author Commented:
no that's the first thing I did...I always "lock" my reference....
0

CINCommented:
Ok, in the file you upload the formula should be:

=IF(MATCH(A1,B2:B1000,0)>0,"Deployed","Not deployed")

if you are going to put more values on the B column you could use a refrence to the entire column instead:

=IF(MATCH(A1,B:B,0)>0,"Deployed","Not deployed")

Ruben
0

Author Commented:
It did not change anything...I am attaching my file for the reference. Thx!
Matching-values2.xlsx
0

CINCommented:
Ok, try this:

IFNA(IF(MATCH(A813,\$Reference.A:A,0)>0,"deployed"),"not deployed")

I use the reference to the entire column

Ruben
0

Author Commented:
I am not following your formula at all: first you put \$ infront of Reference, then you use a period after Reference, after "deployed" you have a parenthesis...I typed what you suggested and it gave me "#NAME?
0

CINCommented:
Sorry about the typo, try this:

=IFNA(IF(MATCH(A813,Reference!A:A,0)>0,"deployed"),"not deployed")

Ruben
0

Author Commented:
Perfect!!! Thank you very much! The other formula just did not work at all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.