Excel Macro to Compare two Columns and write to a third

C

Chris

Hi,

I need to write an Excel Macro that will compare 2 columns of information to
a look up table. So the macro will get the values from Cell E1 and F1 and
look them up in a look up table and if it matches a condition insert a number
into a third column like G1. The macro will go throw all the cells in each
column, for example E1&F2, E2 & F2, E3 & F3, E4 & F4

Example
This is the Look Up table, there are 13 cases all together, and by column# i
mean E1 & F1 & G1, or E3 & F3 & G3
if ( column1 = "cat" & column2 = "on", then column3=1)
if (column1 = "dog" & column2 = "on", then column3 = 2)
if(column1 = "sam" and column2 = "on", then column3 = 3)
else column3 = 0

Thanks

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...a2221be&dg=microsoft.public.excel.programming
 
B

Bernie Deitrick

Chris,

Make a table with your 13 cases, with the values required in the second column (the 1,2, 3 etc),
creating the first column of the table with values like

cat on
dog on

and then use the formula

=IF(ISERROR(VLOOKUP(E1& " " & F1,TableAddress,2,False)),0, VLOOKUP(E1& " " &
F1,TableAddress,2,False))

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top