Vlookup returning more than one result

[Guest]

Dear all,

please i need your help in solving the below problem:

I am preparing a report to summarize the Shortage amount for every employee
in each of the 3 items that we are selling to customers

Sheet 1: Item1 shortages as follow Staff ID Shortage amount

Sheet 2: Item2 shortages as follow Staff ID Shortage amount

Sheet 3: Item3 shortages as follow Staff ID Shortage amount

I need a function to consolidate in sheet4 the shortage for each one of the
staff in the 3 items

Sheet4:

Staff ID Item1 Item2 Item3
xxx xxx xxx xxxx


BR,

Khaled

 

[Guest]

One set-up using SUMIF with INDIRECT which might work here ..

In Sheet1, Sheet2 & Sheet3 (all assumed identically structured)
assume Staff ids are listed in A2 down,
with corresponding shortage amounts in B2 down, eg:

Staff ID Shortage Amt
1111 60
1112 80
1113 80
etc

Then in Sheet4,
assume staff ids are listed in A2 down
and actual sheetnames are listed in B1 across to D1, viz :

Staff ID Sheet1 Sheet2 Sheet3
1111
1112
1113
etc

you could place in B2:
=SUMIF(INDIRECT("'"&B$1&"'!A:A"),$A2,INDIRECT("'"&B$1&"'!B:B"))
Copy B2 to D2, fill down to populate the table.