Search and replace help in Excel

[XAT]

Greetings, i am looking help on following. I have 4 columns in worksheet 1 -
Name, Dept, Salary advance & Salary to be paid, in worksheet 2, I have list
of names. Every month i need to take worksheet 2 containing list of names and
search in worksheet-1, if i find a match need to look at Salary advance
column and adjust this amount in Salary to be paid.

Note:worksheet-1 & 2 contain more than 1000 entries.


Example:
Worksheet-1
Tom,Sales,$200,$2000
Bob,Sales,$0,$2500
Simon,Sales,$1000,$3000

Worksheet-2
Simon
Tom

Result :
Tom,Sales,$0,$1800
Bob,Sales,$0,$2500
Simon,Sales,$0,$2000

Thank you for your time in advance ....

 

[Roger Govier]

Hi
Try entering in Sheet2 B1
=IF($A1="","",VLOOKUP($A1,Sheet1!$A:$D,COLUMN(B1),0)
Copy across through C11
Copy B11 down as far as required.

 

[Roger Govier]

Sorry, that was incorrect.
There is no need for any formula in C1, it can be just =0
D1 it needs to be
=IF($A1="","",VLOOKUP($A1,Sheet1!$A:$D,4,0)-VLOOKUP($A1,Sheet1!$A:$D,3,0))
Copy B11 down as far as required.

 

[XAT]

Thank you for your time on this Roger, certainly your solution was useful if
i have to obtain the result in Worksheet-2.

But i want this to be done in Worksheet -1 itself. I need to use worksheet-2
as my input file and search in worksheet-1, if match found 'To be paid'
column in Worksheet-1 needs to be updated nullifying the value in 'Advance
column'. Eventually, i should be able to get updated worksheet-1.

Regards,
XAT

 

[Roger Govier]

Hi

Then enter the formulae I gave somewhere else on Sheet1