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"
LadkissonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Danny ChildConnect With a Mentor 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
 
David KrollCommented:
=IF(A1=B25, "Deployed", "Not Deployed")
0
 
LadkissonAuthor 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LadkissonAuthor Commented:
Pls refer to the attached file. Thx!
Matching-values.xlsx
0
 
Ruben CornejoCINCommented:
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
 
LadkissonAuthor 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
 
Ruben CornejoCINCommented:
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
 
LadkissonAuthor Commented:
no that's the first thing I did...I always "lock" my reference....
0
 
Ruben CornejoCINCommented:
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
 
LadkissonAuthor Commented:
It did not change anything...I am attaching my file for the reference. Thx!
Matching-values2.xlsx
0
 
Ruben CornejoCINCommented:
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
 
LadkissonAuthor 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
 
Ruben CornejoCINCommented:
Sorry about the typo, try this:

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


Ruben
0
 
LadkissonAuthor Commented:
Perfect!!! Thank you very much! The other formula just did not work at all.
0
All Courses

From novice to tech pro — start learning today.