don't know which function to use

[jgperez78]

hi mr. excel, my apologies but i really cannot find which function i should use. this is the string of logic that i am trying to make a formula for. if A2 is <0.04 and B2 is "D-F" and C2 is "IF-VVS" then A2 should be multiplied by 1250. i know its a lot but i just need to know how to make a formula for this one. please help. thank you SO much in advance.

 

[Michael M]

True, but you didn't define that requirement.....the formula selects the "nearest" value, not the next value Up !

 

[jgperez78]

can we modify the formula according to this requirement?

 

[Michael M]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	WEIGHT (ct)	COLOR	CLARITY	PRICE in USD	$/ct	0	0.039	0.079	0.149	0.179	0.229	0.299	0.399
2	0.08	D - F	IF - VVS	12.5	1200	D - FIF - VVS	1250	1150	1200	1350	1500	1900	2100
3	0.05	G - H	VS	40	800	G - HIF - VVS	1000	900	1000	1200	1350	1700	2000
4	0.09	I - J	IF - VVS	67.5	850	I - JIF - VVS	750	750	850	1000	1100	1400	1900
5	0.16	K - L	IF - VVS	78.4	750	K - LIF - VVS	490	510	670	750	900	1180	1800
6	0.2	M - N	IF - VVS	72	750	M - NIF - VVS	360	380	450	550	750	950	1700
7	0.25	D - F	VS	250	1700	D - FVS	1000	900	1000	1220	1300	1700	1600
8	0.35	D - F	VS	350	1600	G - HVS	850	800	880	1020	1150	1450	1500
Sheet1

Cell Formulas
Range	Formula
E2	=VLOOKUP(CONCATENATE(B2,C2),$F$2:$M$8,MATCH(A2,$F$1:$M$1)+1,0)
E3	=VLOOKUP(CONCATENATE(B3,C3),$F$2:$M$8,MATCH(A3,$F$1:$M$1)+1,0)
E4	=VLOOKUP(CONCATENATE(B4,C4),$F$2:$M$8,MATCH(A4,$F$1:$M$1)+1,0)
E5	=VLOOKUP(CONCATENATE(B5,C5),$F$2:$M$8,MATCH(A5,$F$1:$M$1)+1,0)
E6	=VLOOKUP(CONCATENATE(B6,C6),$F$2:$M$8,MATCH(A6,$F$1:$M$1)+1,0)
E7	=VLOOKUP(CONCATENATE(B7,C7),$F$2:$M$8,MATCH(A7,$F$1:$M$1)+1,0)
E8	=VLOOKUP(CONCATENATE(B8,C8),$F$2:$M$8,MATCH(A8,$F$1:$M$1)+1,0)

 

[Michael M]

Did the last set of formulae work for you ???

 

[jgperez78]

hi michael, it did, thank you so much. i tried to modify it with the look up table on a separate sheet and was able to make it work. thank you sooooo much again and again.

 

[Michael M]

Excellent, glad it worked......another step along the learning path...