Copy adjacent Columns in excel if the ID numbers same

Solved
Report
Posts
3
Registration date
Friday August 25, 2017
Status
Member
Last seen
December 8, 2021
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Dear all,

I have ONE HELP in excel to copy two columns data( Qty & Amount ) from another sheet if the ID numbers are same..

I have created one sheet with the name of "Target sheet" that have all employee details
&I have created another sheet with the name of support sheet.

I want to a formula that if "ID No" are same then Qty and Amount should be copied from support sheet to target sheet.

Also

I want to take attendance of the employee in the same file( Attendance sheet) . so kindly assist me to ta take from "ID No " are same then it will give 1 or P(present), 0 or A(Absent)..

sample document attached dropbox link:
https://www.dropbox.com/scl/fi/8ycbjgikm5djfcwgyca8a/sample-sheet-to-wrok.xlsx?dl=0&rlkey=mkmyz1lfo0n66c7vakxlq9qak

Thanks & Regards,
Ganesh

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Ganesh,

For your first question you have to include the entire source matrix in the VLOOKUP function.
You have: =VLOOKUP(B74,'Support sheet'!$B$3:$B72,2,FALSE)
Which should be (for "qty wotk"): =VLOOKUP(B74,'Support sheet'!$B$3:$D72,2,FALSE)
For "amount" change the 2 into a 3: =VLOOKUP(B74,'Support sheet'!$B$3:$D72,3,FALSE)

For your second question you could do something like this on the attendence sheet:
=IF('TARGET SHEET DEC-21'!H74="",0,1) for the same ID no 75 for the 1st of december.

Best regards,
Trowa