Search and replace help in Excel
 

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 ....

Search and replace help in Excel
 

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

--
Regards
Roger Govier

"XAT" wrote in message
...
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 ....

Search and replace help in Excel
 

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 B1:D1 down as far as required.
--
Regards
Roger Govier

"XAT" wrote in message
...
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 ....

Search and replace help in Excel
 

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" wrote:

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 B1:D1 down as far as required.
--
Regards
Roger Govier

"XAT" wrote in message
...
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 ....

Search and replace help in Excel
 

Hi

Then enter the formulae I gave somewhere else on Sheet1

--
Regards
Roger Govier

"XAT" wrote in message
...
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" wrote:

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 B1:D1 down as far as required.
--
Regards
Roger Govier

"XAT" wrote in message
...
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 ....