Solved: Select Cases using two columns

[sdownes1]

I am a novice and I am trying to use VBA to compare two columns (cell by cell) and place a number in the third column. I am transferring a large number of employees (approx 6000) between companies and need to select the new benefits packages the employees are to recieve in the new company.

I have tried IF statements and Select Case, to no avail. I have attached a file with a sample of data.

Written out, what I am attempting to do is: (see attached file)

IF Column F = ESI AND Column G = EQN THEN column H would be "1"

I believed that this was an easy Select Case but I can't get it to work when comparing TWO columns and placing a number in a third column!

Any advice to get me "jump started" on writing a select case statement would be greatly appreciated!

[Bob Phillips]

Use a formula

=IF(AND(F2="ESI",G2="EQN"),1,"")

[benny]

Hi sdownes1,

Try :
Formula in H2.

IF(AND(F2="ESI",G2="EQN"),1,"")

and copy down

[sdownes1]

Thanks very much!
But I have multiple combinations, for example, F2 = "ENOI" and G2 = "EQL" which would = 3 in H1, would I need to nest all the variations in the formula or is there an other way

[Bob Phillips]

best way would be to build a table with all combinations, say

ESIEQN 1
ENOIEQL 3
etc, in say K & L and use

=IF(ISNA(VLOOKUP(F2&G2,$K$1:$L$10,2,FALSE)),"",VLOOKUP(F2&G2,$K$1:$L$10,2,F ALSE))

There is a space that gets injected into the second false, remove that.

[sdownes1]

XLD - That is great! I appreciate the assistance! I have been banging my head against the wall for weeks! I truly appreciate it!