Help with Formula

[helenshas]

The first table is the hours that staff work during the month, the second table is there hourly rate, I did a vlookup that search's for the employee number then calculates this from a sage download file, on the third table I need a formula that does a search on the hourly rate and in the same formula search's for how many hours they have done, as I need to put this amount in each Department, is there such a thing as a double Vlookup in a formula or can I do something else?

The staff may not be in the same order, as there are 100 staff,
=VLOOKUP(A2,Hours!A2:Z166,Mth+25,FALSE)*'Hourly Rate'!E2 is the normal method but if they are not in the same order it will not work
I need a search on the hourly rate to find the person and input the figure
=VLOOKUP(A2,Hours!A2:Z166,Mth+25,FALSE)=(VLOOKUPA2,'Hourly Rate'!A2:L158,Mth+25,FALSE) something like this is needed but it does not work, any suggestions please to be put in the last grid if that makes sense
Hours

0​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

KEN

11​

Jane Mac

200​

23​

Julie Mac

161​

8​

7​

24​

​

​

​

​

​

​

​

​

Hourly Rate

			Hourly Rate	PAYE	EMPLOYEE NIC	EMPLOYEE PENSION	Studant Loans	Attachments
11​	Jane Mac	MACDONALD	21.67	3.83	2.13	0.68	-	-
23​	Julie Mac	WHITE	14.08	1.85	1.36	0.47	-	-

0​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

11​

Jane Mac

766.00

23​

Julie Mac

 

[Candyman8019]

Without seeing a copy of your full spreadsheets, here's where I think you're going.

Your formula in column C would then look something like:
=XLOOKUP($A$2,Hours!$A$2:$A$166,C2:C166)*XLOOKUP($A$2,'Hourly Rate'!$A$2:$A$158,'Hourly Rate'!$D$2:$D$158)

So, this will essentially take total hours from the hours table tab month 0 and multiply it by the appropriate rate in the hourly rate tab. Copy the formula across to the remaining months and you should see results.

 

[KRice]

I am not quite clear on what you want to do. I'm guessing the final table headings 0-12 represent months...but I don't know what the 0 column is for. Here is an approach using INDEX/MATCH to match both employee ID and the month(?) number to get the hours(?), which are then multiplied by the hourly rate.

MrExcel_20220505B.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			0	1	2	3	4	5	6	7	8	9	10	11	12
2	11	Jane	4334.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	23	Julie	0.00	2266.88	0.00	0.00	112.64	0.00	0.00	0.00	0.00	0.00	98.56	0.00	0.00
Summary

Cell Formulas
Range	Formula
C2:O3	C2	=INDEX(Hours!$C$2:$O$200,MATCH($A2,Hours!$A$2:$A$200,0),MATCH(C$1,Hours!$C$1:$O$1,0))*INDEX('Hourly Rate'!$D$2:$D$200,MATCH($A2,'Hourly Rate'!$A$2:$A$200,0))

 

[helenshas]

Without seeing a copy of your full spreadsheets, here's where I think you're going.

Your formula in column C would then look something like:
=XLOOKUP($A$2,Hours!$A$2:$A$166,C2:C166)*XLOOKUP($A$2,'Hourly Rate'!$A$2:$A$158,'Hourly Rate'!$D$2:$D$158)

So, this will essentially take total hours from the hours table tab month 0 and multiply it by the appropriate rate in the hourly rate tab. Copy the formula across to the remaining months and you should see results.Hi

HI thank you very much, sorry I have not written sooner,
would the xlookup also work with my other formula question that I have just posted on adding department tabs? Would it also work with my ,Mth+25,FALSE) as well please
Helen

 

[Candyman8019]

What exactly do you want to do with MTH+25? In your formula, that is in the column index number position...I'm assuming that is not what you intend. Can you explain further?

 

[KRice]

In Post #1 you mention searching for hourly rate, but in the Summary sheet you show only one number (766.00 for Employee 11). The 766.00 appears to be calculated as the product of 200 hours in column "0" of the Hours sheet and 3.83 in the "PAYE" column on the Hourly Rate sheet. And the hourly rate of 21.67 on the Hourly Rate sheet is never used! Is this correct?...I don't understand what you want. Could you please fill out (manually type in the expected results) on the Summary sheet from your Post #1 and show where MTH+25 comes from? It may be defined in the Formula > Name Manager under the "Refers To" field.

Also the Hours sheet has columns of 0-12 while the Summary sheet has columns 0-11. Are these supposed to be the same? Are these month numbers? What is column 0? Where is column 12 in the Summary sheet? In my offering in Post #3, for any Employee ID on the Summary sheet, their Hourly Rate is taken from the Hourly Rate sheet (not the PAYE value...that may not be correct, but clarification is needed). Then the Hourly Rate is multiplied by the number of hours found under the same column whose heading matches (column 0 of Summary is matched to column 0 of Hours, etc.).