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.

 

[jgperez78]

yes you did thank you so much, i was actually dreading that you would have been fed up with me. thanks again.

 

[Michael M]

No worries...
When you've read a few threads on this forum, you kinda get a feel for what the poster is gonna throw at you !!
Sometimes there are a number of different ways of doing things.

Glad it worked...

 

[jgperez78]

hi michael, sorry but i need help again. i'm having trouble with the column index number, on the formula you gave me it is only for 2 columns and now i have multiple. can't figure out, please save me again. Thank you.

WEIGHT (ct) COLOR CLARITY PRICE in USD $/ct 0.01 - 0.03 0.04 - 0.07 0.08 - 0.14 0.15 - 0.17 0.18 - 0.22 0.23 - 0.29 0.3 - 0.39 0.01 D - F IF - VVS 12.5 $1,250.00 D - FIF - VVS 1250 1150 1200 1350 1500 1900 2100 0.05 G - H VS 40 $800.00 G - HIF - VVS 1000 900 1000 1200 1350 1700 2000 0.09 I - J IF - VVS 67.5 $750.00 I - JIF - VVS 750 750 850 1000 1100 1400 1900 0.16 K - L IF - VVS 78.4 $490.00 K - LIF - VVS 490 510 670 750 900 1180 1800 0.20 M - N IF - VVS 72 $360.00 M - NIF - VVS 360 380 450 550 750 950 1700 0.25 D - F VS 250 $1,000.00 D - FVS 1000 900 1000 1220 1300 1700 1600 0.35 D - F VS 350 $1,000.00 G - HVS 850 800 880 1020 1150 1450 1500 <tbody> </tbody>

<tbody>
</tbody>

 

[Michael M]

I don't have Excel at the moment, so can't help, but a couple of points to note
1. A standard VLOOKUP won't work in this case
2. The Column Index in a modified VLOOKUP will require the use of INDEX MATCH to ID the column where the correct data sits
3. Your headings for your weight ranges will need to change to numerical data, ie, 0.01-0.03 is not an appropriate numerical data, it will have to be something like 0.030 or 0.040, etc
4. the match formula won't work on the data as mentioned in note 3

 

[jgperez78]

Hi Michael, i guess the only thing i can do is change the formula accordingly per row to a specified column. Like if one row specifies from 0.01 - 0.03 then the lookup will be from column J-K and the next row will be from 0.04 - 0.07 and the lookup will be from column L-M. Thanks a lot again for the notes. Will try to work on it if it will give the result i want. But will continue to look for ways to modify the formula that will be easier to follow for the user who didn't make the worksheet. I guess i will just have to specify that one can only enter specific weights per specific row as not to have problems with the result based on the formula. Thank you SO very much, as always.

 

[Michael M]

just got back to my computer
Here's an example, of what you may need to modify your worksheet to look like !!
You can see how changing your needs affects the layout AND formulae

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	1150	D - FIF - VVS	1250	1150	1200	1350	1500	1900	2100
3	0.05	G - H	VS	40	850	G - HIF - VVS	1000	900	1000	1200	1350	1700	2000
4	0.09	I - J	IF - VVS	67.5	750	I - JIF - VVS	750	750	850	1000	1100	1400	1900
5	0.16	K - L	IF - VVS	78.4	670	K - LIF - VVS	490	510	670	750	900	1180	1800
6	0.2	M - N	IF - VVS	72	550	M - NIF - VVS	360	380	450	550	750	950	1700
7	0.25	D - F	VS	250	1300	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
Sheet2

Cell Formulas
Range	Formula
E2	{=VLOOKUP(CONCATENATE(B2,C2),$F$2:$M$8,MATCH(MIN(ABS($F$1:$M$1-A2)),ABS($F$1:$M$1-A2),0),0)}
E3	{=VLOOKUP(CONCATENATE(B3,C3),$F$2:$M$8,MATCH(MIN(ABS($F$1:$M$1-A3)),ABS($F$1:$M$1-A3),0),0)}
E4	{=VLOOKUP(CONCATENATE(B4,C4),$F$2:$M$8,MATCH(MIN(ABS($F$1:$M$1-A4)),ABS($F$1:$M$1-A4),0),0)}
E5	{=VLOOKUP(CONCATENATE(B5,C5),$F$2:$M$8,MATCH(MIN(ABS($F$1:$M$1-A5)),ABS($F$1:$M$1-A5),0),0)}
E6	{=VLOOKUP(CONCATENATE(B6,C6),$F$2:$M$8,MATCH(MIN(ABS($F$1:$M$1-A6)),ABS($F$1:$M$1-A6),0),0)}
E7	{=VLOOKUP(CONCATENATE(B7,C7),$F$2:$M$8,MATCH(MIN(ABS($F$1:$M$1-A7)),ABS($F$1:$M$1-A7),0),0)}
E8	{=VLOOKUP(CONCATENATE(B8,C8),$F$2:$M$8,MATCH(MIN(ABS($F$1:$M$1-A8)),ABS($F$1:$M$1-A8),0),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jgperez78]

hi michael, now i understand what you mean. i should keep reading the forums you have here. its almost the same as going to school , thanks teacher.

 

[jgperez78]

hi michael, just noticed, looking at the excel sheet E3 should be 800 (based on the criteria 0.05,H1 and G - HVS which would be H8), but based on the formula it gave G8 which would be looking at the wrong weight. did i misunderstood again?

 

[Michael M]

Looks Ok to me....keeping in mind the B3 & C3 equate to G - HVS in column F....not G - HIF - VVS as you are probably reading !!

 

[jgperez78]

yes it is F8 (G - HVS) but based on its weight 0.05, it should take the price of H8 (coz its under 0.079 but above 0.039)