IF Then across 2 sheets

[diggory]

Hey, having trouble figuring out how to run this code. I'm not nearly as excel savy as most of you so bare with me...

I want to reference sheet1 A!4 with sheet3 A!4. If these values match, I want to "plug in" the values from the corresponding row on sheet 3 into sheet 1.

For example. IF sheet1 A5 = sheet3 A9... then sheet3 E9, sheet3 F9, sheet3 G9 should be inserted into sheet1 H5, sheet1 I5, sheet1 J5 respectively.

If A5 does not equal any values in sheet 3 A# then leave H I J blank.

I have been trying to run as a macro under visual basic. Here is what I have started, but it has not gotten me anywhere:

If Value(Sheet1!A!4)= Value(Sheet3!A!4) Then
value(sheet1! H!4)=Sum(sheet3! E!9),
value(sheet1! I!4)=Sum(sheet3! F!9),
value(sheet1! I!4)=Sum(sheet3! G!9),
ElseIf (Value(Sheet1!A!4)<>Value(sheet3!A!4)," ")
End If

 

[mikerickson]

The VLOOKUP part of the formula should return the values if Larry was found and "Sheet2!" if not.

Note that the value in Sheet2 column A has to match exactly (case insensitive) e.g. when looking for "Larry" it will not find "Larry Smith". To find "Larry Smith" one would look for either
"Larry" & "*"
or
"*" & "Larry" & "*"

 

[diggory]

Is there any way to do this with the case insensitive?

ie, In sheet 1, I have cells D and E with first and last names all upper case. I want to search for these names with random case in sheet2 and retrieve respective values in each row to insert in sheet 1 as explained before?

*Reminder, Excel 2003*

 

[diggory]

I am upgrading to 2007!

Still having trouble searching for names though. I want to search for the names in B of sheet1 and find them in sheet2, then import values as time in sheet1.

 

[diggory]

Bump... help

Can only get it to read "sheet2!"