Vlookup or Index match multiple criteria

[Caly]

Hi I have a table with cost data that I’m using index match for but now I want to add to that additional criteria to apply but struggling with applying the vlookup if that is correct or not.

This is the table chart for the cost data in columns A through C:
Col A. Col B. Col C.
1. Start. End. Floor.
2. $0.00. $2.00. 35%
3. $2.01. $4.00. 18%

The initial formula was just looking at the cost of the item to bring in what the floor percent value is for it based on its cost. And this is in the data tab with the formula in column C. Below is that formula:
B:
Col A. Col B. Col C.
1. Category. Cost. Formula for the floor
2. Apple. $2.60. 18%

=if(index(‘cost tab’!$C$2:$C$3,
match($C2,’cost tab’!$B$2:$B$3,1))

But now I need to also look at if the category says Apple or anything else added to a tab taht will have different categories and then values for those taht will be different from the standard values in the cost tab. I was trying to do a vlookup but I’m getting a SPILL error message.

Below is the category table

Col A Col B
1. Category. Percentage
2. Apple. 10%
3. Grape. 2%
4. Kiwi. 5%


I was trying to do this in front of the other formula but getting an error message of #SPILL!
=if($A2=vlookup(‘category table’!$A$2:$A$4,1,false),vlookup($A2,’category table’!$A$2:$B$4,2,false),

Thank you so much

 

[GraH]

Maybe something like below?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	category	cost	floor				End	Floor			Category	Pct
2	apple	2,6	10%				0	35%			apple	10%
3	kiwi	3,4	5%				2	35%			grape	2%
4	orange	5,1	18%				4	18%			kiwi	5%
5	pear	1,98	35%
6
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IFNA(XLOOKUP(A2:A5,$K$2:$K$4,$L$2:$L$4),XLOOKUP(B2:B5,$G$2:$G$4,$H$2:$H$4,,-1,2))
Dynamic array formulas.

Strangely enough without IFNA( ), using the build in "if not found" of XLOOKUP( ), gives another result.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	category	cost	floor				End	Floor			Category	Pct
2	apple	2,6	10%				0	35%			apple	10%
3	kiwi	3,4	5%				2	35%			grape	2%
4	orange	5,1	35%				4	18%			kiwi	5%
5	pear	1,98	35%
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=XLOOKUP(A2:A5,$K$2:$K$4,$L$2:$L$4,XLOOKUP(B2:B5,$G$2:$G$4,$H$2:$H$4,,-1,2))
Dynamic array formulas.