VLOOKUP, XLOOKUP or INDEX?

[mrstivoaddict]

I am currently using VLOOKUP to return a value from one tab to another.

=VLOOKUP(E2,Rates!$B$3:$C$57,2,0)

I would like to add additional conditions to this formula so that it will look up the values of each cell in column A and B to return the corresponding value from the Rates sheet into column C.
I'm trying to decide what's the best way to do it.

DETAILS

POSITION (A)	TYPE (B)	RATE (C)
MANAGER	ST
SUPERVISOR	OT
WORKER	ST
WORKER	DT
SUPERVISOR	DT
WORKER	DT

RATES

POSITION	ST	OT	DT
MANAGER	172	220	294
SUPERVISOR	169	217	290
WORKER	150	175	200

So...if vlookup finds Manager and ST, it will return 172, Supervisor and DT will return 290, Worker and OT will return 175, etc.

Suggestions would be much appreciated!

 

[6StringJazzer]

I think this should do it but I did not set up a test case.

=INDEX(Sheet2!$A$1:$D$3,MATCH($A2,Sheet2!$A:$A,0),MATCH($B2,Sheet2!$1:$1,0))

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

mrstivoaddict.xlsm
	A	B	C	D
1	POSITION	ST	OT	DT
2	MANAGER	172	220	294
3	SUPERVISOR	169	217	290
4	WORKER	150	175	200
RATES

mrstivoaddict.xlsm
	A	B	C
1	POSITION (A)	TYPE (B)	RATE (C)
2	MANAGER	ST	172
3	SUPERVISOR	OT	217
4	WORKER	ST	150
5	WORKER	DT	200
6	SUPERVISOR	DT	290
7	WORKER	DT	200
DETAILS

Cell Formulas
Range	Formula
C2:C7	C2	=SUMPRODUCT((RATES!A$2:A$4=A2)*(RATES!B$1:D$1=B2),RATES!B$2:D$4)

 

[mrstivoaddict]

Welcome to the MrExcel board!

Does this do what you want?

mrstivoaddict.xlsm
	A	B	C	D
1	POSITION	ST	OT	DT
2	MANAGER	172	220	294
3	SUPERVISOR	169	217	290
4	WORKER	150	175	200
RATES

mrstivoaddict.xlsm
	A	B	C
1	POSITION (A)	TYPE (B)	RATE (C)
2	MANAGER	ST	172
3	SUPERVISOR	OT	217
4	WORKER	ST	150
5	WORKER	DT	200
6	SUPERVISOR	DT	290
7	WORKER	DT	200
DETAILS

Cell Formulas
Range	Formula
C2:C7	C2	=SUMPRODUCT((RATES!A$2:A$4=A2)*(RATES!B$1:D$1=B2),RATES!B$2:D$4)

Thank you so much, this is exactly what I needed. Now I have to figure out why it's not working in my spreadsheet!

 

[Peter_SSs]

Now I have to figure out why it's not working in my spreadsheet!

In what way is it not working for you? Perhaps all the formulas are returning 0 because the values in 'RATES' are Text not actual Numbers? If so, try this instead

=SUMPRODUCT((RATES!A$2:A$4=A2)*(RATES!B$1:D$1=B2),--RATES!B$2:D$4)

 

[mrstivoaddict]

They are formatted as numbers, I just double checked. At least if it gave me an error I would know where to start!

 

[Peter_SSs]

They are formatted as numbers

Formatting as numbers does not necessarily mean the underlying values are numbers. Did you try the formula from post #5?

We cannot tell much from an image (we cannot see what worksheet or what range that is) and we cannot copy from it for testing, and we certainly cannot see the other worksheet. Perhaps you could give small samples of both worksheets with XL2BB like I did?
(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

Failing that can you upload a small sample workbook where the formula is failing to DropBox or OneDrive or Google Drive etc and provide a publicly shared link here?

 

[mrstivoaddict]

Formatting as numbers does not necessarily mean the underlying values are numbers. Did you try the formula from post #5?

We cannot tell much from an image (we cannot see what worksheet or what range that is) and we cannot copy from it for testing, and we certainly cannot see the other worksheet. Perhaps you could give small samples of both worksheets with XL2BB like I did?
(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

Failing that can you upload a small sample workbook where the formula is failing to DropBox or OneDrive or Google Drive etc and provide a publicly shared link here?

I did try the other formula, same result. I will try to upload a sample.

 

[mrstivoaddict]

Dropbox

www.dropbox.com

 

[Peter_SSs]

Thanks for access to the file. There are two issues:

1. The values in 'Rates' C3:E3 are not just "ST", "OT" and "DT", they each have 9 space characters after them - see my LEN() formulas below. So get rid of the space characters in those headings.
   
   
2. The second problem is your formula ...
   =SUMPRODUCT((Rates!B$5:B$54=C8)*(Rates!C$4:E$4=E8),Rates!C$5:E$54)
   ... it is looking for those ST/OT/DT headings in row 4 when they are actually in row 3 of 'Rates'

mrst SAMPLE.xlsx
	B	C	D	E
1		11	11	11
2	LABOR RATES
3	POSITION OR TITLE	ST	OT	DT
4	N/A	0.00	0.00	0.00
5	Manager	150.00	175.00	200.00
6	Supervisor	125.00	150.00	175.00
7	Worker	100.00	125.00	150.00
Rates

Cell Formulas
Range	Formula
C1:E1	C1	=LEN(C3)