Excel Macro to Compare two Columns and write to a third

[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

 

[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