Lookup with 2 criterias

[feni1388]

Hello...

As the image that I uploaded, I need to do a lookup with 2 criterias.
I'm combining if and vlookup together to get the result.
But the thing is that the rank sometimes changes to A4 or A6 and so on.
When the rank changes, I just need to paste a new table but then I also need to change the current formula that I'm using.
Is there any formula that won't need any adjustment even if the rank changes?
If possible, I don't want to use xlookup as there're still some old version of excel in some computers that can't use it.

Thank you.

 

[feni1388]

Ahh.... thank you... I didn't realise that I changed the formula.
Sorry, I tried using XL2BB but somehow I couldn't extract it after downloading it to my computer.

Anyway, regarding to the formula. Is it going to be different if the price list is on another sheet? because when I'm using it like this, then the result is off.
The first few items on the top will be OK.... but the ones after that will be somehow off although sometimes is OK.

 

[feni1388]

Cost

500

500

600

800

Rank

S5

E5

A5

C5

vlookup

index

C5

Item

Selling price

Carton

Selling price

Carton

Selling price

Carton

Selling price

Carton

AB02

440​

440​

AB05

94​

94​

AB02

425​

10,200​

430​

10,320​

435​

10,440​

440​

10,560​

AS05

320​

320​

AB03

345​

8,280​

350​

8,400​

355​

8,520​

360​

8,640​

RF48

145​

203​

off

AB05

88​

5,280​

90​

5,400​

92​

5,520​

94​

5,640​

GE30

180​

138​

off

AB06

62​

7,440​

63​

7,560​

64​

7,680​

65​

7,800​

JF10

348​

118​

off

AB07

345​

8,280​

350​

8,400​

355​

8,520​

360​

8,640​

BM13

55​

55​

OK

AB44

72​

8,640​

73​

8,760​

74​

8,880​

75​

9,000​

GL06

204​

84​

off

AS02

60​

3,600​

63​

3,780​

66​

3,960​

66​

3,960​

AS03

60​

3,600​

63​

3,780​

66​

3,960​

66​

3,960​

AS05

300​

7,200​

310​

7,440​

320​

7,680​

320​

7,680​

AS15

67​

5360​

68​

5440​

69​

5520​

70​

5600​

AS25

92​

5520​

94​

5640​

96​

5760​

98​

5880​

AS44

160​

6400​

162​

6480​

164​

6560​

166​

6640​

AS45

148​

5920​

150​

6000​

152​

6080​

155​

6200​

GL06

79​

4740​

80​

4800​

82​

4920​

84​

5040​

GL07

76​

4560​

77​

4620​

79​

4740​

81​

4860​

GL08

76​

4560​

77​

4620​

79​

4740​

81​

4860​

GL09

76​

4560​

77​

4620​

79​

4740​

81​

4860​

BM11

53​

5300​

54​

5400​

55​

5500​

56​

5600​

BM12

52​

5200​

53​

5300​

54​

5400​

55​

5500​

BM13

52​

5200​

53​

5300​

54​

5400​

55​

5500​

BM14

52​

5200​

53​

5300​

54​

5400​

55​

5500​

 

[Peter_SSs]

Sorry, I tried using XL2BB but somehow I couldn't extract it after downloading it to my computer.

Have a look at the issues discussed here. Solves problems for most users.

ones after that will be somehow off although sometimes is OK.

Ah, I see the issue now, I had omitted a ",0" from my formula - see below.

Is it going to be different if the price list is on another sheet?

Should be no problem for either formula if the two sections are on different sheets.

feni1388.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Cost	500		500		600		800
2
3	Rank	S5		E5		A5		C5
4
5
6	Item	Selling price	Carton	Selling price	Carton	Selling price	Carton	Selling price	Carton
7
8	AB02	425	10,200	430	10,320	435	10,440	440	10,560
9	AB03	345	8,280	350	8,400	355	8,520	360	8,640
10	AB05	88	5,280	90	5,400	92	5,520	94	5,640
11	AB06	62	7,440	63	7,560	64	7,680	65	7,800
12	AB07	345	8,280	350	8,400	355	8,520	360	8,640
13	AB44	72	8,640	73	8,760	74	8,880	75	9,000
14	AS02	60	3,600	63	3,780	66	3,960	66	3,960
15	AS03	60	3,600	63	3,780	66	3,960	66	3,960
16	AS05	300	7,200	310	7,440	320	7,680	320	7,680
17	AS15	67	5360	68	5440	69	5520	70	5600
18	AS25	92	5520	94	5640	96	5760	98	5880
19	AS44	160	6400	162	6480	164	6560	166	6640
20	AS45	148	5920	150	6000	152	6080	155	6200
21	GL06	79	4740	80	4800	82	4920	84	5040
22	GL07	76	4560	77	4620	79	4740	81	4860
23	GL08	76	4560	77	4620	79	4740	81	4860
24	GL09	76	4560	77	4620	79	4740	81	4860
25	BM11	53	5300	54	5400	55	5500	56	5600
26	BM12	52	5200	53	5300	54	5400	55	5500
27	BM13	52	5200	53	5300	54	5400	55	5500
28	BM14	52	5200	53	5300	54	5400	55	5500
29
Sheet3

The #N/A results below are simply because the values in col B are not included in the above sheet's data in my sample.
Note the extra ,0 near the end of the col C formula

feni1388.xlsm
	A	B	C	D
1
2			A5
3			vlookup	index
4		AB02	435	435
5		AS05	320	320
6		RF48	#N/A	#N/A
7		GE30	#N/A	#N/A
8		JF10	#N/A	#N/A
9		BM13	54	54
10		GL06	82	82
11
Sheet4

Cell Formulas
Range	Formula
C4:C10	C4	=VLOOKUP(B4,Sheet3!A$8:I$836,MATCH(C$2,Sheet3!A$3:I$3,0),0)
D4:D10	D4	=INDEX(Sheet3!$B$8:$I$836,MATCH(B4,Sheet3!$A$8:$A$836,0),MATCH($C$2,Sheet3!$B$3:$I$3,0))

 

[feni1388]

Ah, I see the issue now, I had omitted a ",0" from my formula - see below.

I see....
Thank you. I tried the revised formula and it works ....

 

[Peter_SSs]

Cheers.