Excel compare and match values to assign a new value

[gk759]

Hi!!

I am looking for an excel fomula to accomplish the following. I have two cloumns with information and if I have matching value in the 2 columns I would like to assign a value to the corresponding cell otherwise it will update the cell with what's in column2.

Example:

Column1 Column2 Formula
-----------------------------------------------
Buffalo NewYork ChickenWings
Cleveland Ohio Ohio
Seattle Washington Washington
Baltimore Maryland Maryland
Buffalo NewYork ChickenWings
Houston Texas Texas

I have tried various combinations of IF, ISNA and VLOOKUP but somehow I am unable to get the above result. I would appreciate help to resolve this.

Thanks in advance!!

Cheers

Gary

 

[SkipVought]

Hi,

Please post some of your failed attempts.

"... if I have matching value in the 2 columns ..."

WHAT are you matching with WHAT?

I'm at a loss, because I can't see what you see or think what you think. So you're gonna havta be CLEAR, CONCISE & COMPLETE in your description of the problem.

Skip,

_[red][/red]

 

[Gavona]

Of course Skip is correct. But with the following tips you could have another go yourself and then post back (hopefully with the working formula). If you describe what you are trying to achieve rather better you may even get tips about alternative approaches that may be more efficient.

My suggestion: have a go testing each list independently using MATCH and combine results using AND

something like this.
if(and(isna(match(....),isna(match(....)),"nomatch","chickenwings")

However, start simple with a formula (lets assume in D3 just using Match on the first list. Once you have that working and understand how it works then add the Isna "wrapping".
Make sure it gives the right results when copied down.

In E3 replicate the formula but using match on the second list.

Then in F3 try = and(D3,E3).
Once happy with the result then in C3 type
=IF(F3,"nomatch,"chickenwings")

Now you can combine the different formulae into one eg by copying [Ctrl-C] from the edit window of F3 and pasting [Ctrl-v]into the edit window of C3.

Hope that helps you get started.


Gavin

 

[gk759]

Skip/Gavin

Sorry for the confusion, maybe I wasn't clear in my explanation. From the example. I want to match data from column 1 and column 2 having 'Buffalo' and 'NewYork' to update the formula column with 'Chicken Wings' and every thing other than 'Buffalo' and 'New York' will be updated with information in Column2 only. The table is the end result I am hoping to accomlish with the formula column containing the output of the formula.

Column1 Column2 Formula
-----------------------------------------------
Buffalo NewYork ChickenWings
Cleveland Ohio Ohio
Seattle Washington Washington
Baltimore Maryland Maryland
Buffalo NewYork ChickenWings
Houston Texas Texas

Hope this helps.I am experimenting with your suggestions now. I will let you know if I am able to accomplish the task.

thanks,

Gary

 

[Gavona]

if(and(a1="Buffalo",b1="NewYork"),"ChickenWings",b1)


Gavin

 

[gk759]

Gavin,

Thank you so much!! It works now!!

Cheers,

Gary