Vlookup and Match problem

1 Attachment(s)

Please see attached file. When the variable in cell C2 of the "Working" sheet is changed to the number 1, the responses under "DMG" error out. If you change C2 to a 2 there is no problem. My formulas seem to be correct but I cannot figure out why this is erroring out. Especially, when it doesn't happen with the HTECH and FE columns of data.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hello bobkap12 , Welcome to ExcelFox

Quote:

Originally Posted by bobkap12

When the variable in cell C2 of the "Working" ….. If you change C2 …..

????? –
I think maybe you mean C6 ?
https://i.postimg.cc/gwFKHHch/C2.jpg

I made a start and you can see how I approach the problem, see my notes here: https://excelfox.com/forum/showthrea...ll=1#post16713
https://excelfox.com/forum/showthrea...ll=1#post16721

I notice a couple of strange things on first investigations
_1) some character combinations are not working as the look up value, such as , for example "dmg1"
_2) In addition, the actual value given when the VLookUp is working appear to be incorrect sometimes.

I am puzzled.

I have no more time to look at this today.
Maybe someone else can shed some light on these puzzling results.
If you and no one else can solve the problem in the next few days, then I will take another look when I have more time.

Sorry I don’t have a solution to the problem yet.

My next step would be to step by step, reduce and simplify the data whilst maintaining the strange problems. This would usually make it much easier to isolate the problem

Alan

Code:

=VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1)

your bk (table_array) doesn't contain DMG1 (lookup_value) - result: #N/A

Quote:

Originally Posted by sandy666

Code:

=VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1)

your bk (table_array) doesn't contain DMG1 (lookup_value) - result: #N/A

Hi there :) :cool:
I don’t understand? :confused:
I thought this was table bk ?
https://postimg.cc/mthjqfLQ https://i.postimg.cc/Hs9NrdFP/bk-Table-data-A1-M13.jpg
https://i.postimg.cc/Hs9NrdFP/bk-Table-data-A1-M13.jpg

What am I missing?

=VLOOKUP(F$26,{"Part",13,25,37,48,73,101,145,201,2 89,600,750,1009;"DMG1",4.1,2.85,2.85,1.9,1.55,1.35 ,1.25,0.95,0.85,0.75,0.75,0.65;"DMG2",5.3,3.75,2.8 5,2.65,2.1,1.8,1.65,1.25,1.1,0.9,0.9,0.75;"DMG3",7 .05,4.6,4.6,3.45,2.65,2.15,1.9,1.5,1.25,1.05,1.05, 0.9;"DMG4",8.05,5.95,5.95,4.1,3.2,2.45,2.3,1.75,1. 4,1.2,1.2,1;"FE1",2.25,2.25,1.6,1.6,1.4,1.4,1.2,1. 2,1,0.95,0.95,0.9;"FE2",2.35,2.35,1.8,1.8,1.55,1.5 5,1.4,1.4,1.05,1,1,0.95;"FE3",2.55,2.55,2,2,1.7,1. 7,1.6,1.6,1.1,1.05,1.05,1;"FE4",2.75,2.75,2.2,2.2, 1.85,1.85,1.75,1.75,1.15,1.1,1.1,1.05;"HTECH1",2.8 8,1.77,1.77,1.77,1.14,1.14,0.91,0.91,0.78,0.66,0.6 ,0.55;"HTECH2",4.6,2.25,2.25,2.25,1.43,1.43,1.28,1 .28,1.02,0.84,0.76,0.69;"HTECH3",6.66,2.74,2.74,2. 21,1.71,1.62,1.62,1.62,1.27,1.02,0.93,0.84;"HTECH4 ",8.05,3.11,3.11,2.5,2.04,2.04,1.98,1.98,1.51,1.2, 1.09,0.98},6)

where is DMG1 on index = 6 ?

I see DMG1 on index = 14

with formula =VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0) value $1.55 is returned and so on

OP just forgot comma on the end or define last argument 0, VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,) so he need to learn how to use VLOOKUP function
VLOOKUP require all arguments, even if last argument is omitted there should be defined place for it after last comma

I suggest to use INDEX / MATCH instead of VLOOKUP

Decorating Cost Each

Screen Costs Each

Cost Each Total

Total Cost For Quantity

Sell price each @ objective

Size

QTY

Apparel Cost EA

HTECH

FE

DMG

HTECH

FE

DMG

HTECH

FE

DMG

HTECH

FE

DMG

HTECH

FE

DMG

XS

10

$ 10.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 11.17

$ 11.75

$ 11.55

$ 111.71

$ 117.50

$ 115.50

$ 18.02

$ 18.95

$ 18.63

S

10

$ 10.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 11.17

$ 11.75

$ 11.55

$ 111.71

$ 117.50

$ 115.50

$ 18.02

$ 18.95

$ 18.63

M

10

$ 10.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 11.17

$ 11.75

$ 11.55

$ 111.71

$ 117.50

$ 115.50

$ 18.02

$ 18.95

$ 18.63

L

10

$ 10.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 11.17

$ 11.75

$ 11.55

$ 111.71

$ 117.50

$ 115.50

$ 18.02

$ 18.95

$ 18.63

XL

10

$ 10.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 11.17

$ 11.75

$ 11.55

$ 111.71

$ 117.50

$ 115.50

$ 18.02

$ 18.95

$ 18.63

2XL

10

$ 11.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 12.17

$ 12.75

$ 12.55

$ 121.71

$ 127.50

$ 125.50

$ 19.63

$ 20.56

$ 20.24

3XL

10

$ 12.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 13.17

$ 13.75

$ 13.55

$ 131.71

$ 137.50

$ 135.50

$ 21.24

$ 22.18

$ 21.85

4XL

10

$ 13.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 14.17

$ 14.75

$ 14.55

$ 141.71

$ 147.50

$ 145.50

$ 22.86

$ 23.79

$ 23.47

5XL

0

$ 14.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.35

$ -

$ 15.17

$ 15.75

$ 15.55

$ -

$ -

$ -

$ 24.47

$ 25.40

$ 25.08

6XL

0

$ 17.00

$ 1.14

$ 1.40

$ 1.55

$ 0.03

$ 0.03

$ -

$ 18.17

$ 18.43

$ 18.55

$ -

$ -

$ -

$ 29.31

$ 29.73

$ 29.92

80

$ 953.70

$ 1,000.00

$ 984.00

Quote:

Originally Posted by sandy666

with formula =VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0) value $1.55 is returned and so on
..just forgot comma on the end or define last argument 0, VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,) so he need to learn how to use VLOOKUP function
VLOOKUP require all arguments, even if last argument is omitted there should be defined place for it after last comma

https://i.postimg.cc/ncHVbyD8/ThatsIt.gif
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )

But just out of interests, and for me to learn better, 2 things you said I don’t understand……

_1

Quote:

Originally Posted by sandy666

where is DMG1 on index = 6 ?

Why you say this? DMG1 is LookUp_Value so is in column A ( index 1 )
What am I missing again?

_2

Quote:

Originally Posted by sandy666

I see DMG1 on index = 14

Why you say this? There is no index 14? Index 14 would be column N, but column N is not in range A1:M13 ( maximum column_index for table_array range A1:M13 is 13 )
What am I missing again?

Can you help me understand what I am missing, Thx :)

( I also do not understand why it works sometimes? )

VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0)

bk range lookup
{"Part",13,25,37,48,73,101,145,201,2 89,600,750,1009;"DMG1",4.1,2.85,2.85,1.9,1.55,1.35 ,1.25,0.95,0.85,0.75,0.75,0.65;"DMG2",5.3,3.75,2.8 5,2.65,2.1,1.8,1.65,1.25,1.1,0.9,0.9,0.75;"DMG3",7 .05,4.6,4.6,3.45,2.65,2.15,1.9,1.5,1.25,1.05,1.05, 0.9;"DMG4",8.05,5.95,5.95,4.1,3.2,2.45,2.3,1.75,1. 4,1.2,1.2,1;"FE1",2.25,2.25,1.6,1.6,1.4,1.4,1.2,1. 2,1,0.95,0.95,0.9;"FE2",2.35,2.35,1.8,1.8,1.55,1.5 5,1.4,1.4,1.05,1,1,0.95;"FE3",2.55,2.55,2,2,1.7,1. 7,1.6,1.6,1.1,1.05,1.05,1;"FE4",2.75,2.75,2.2,2.2, 1.85,1.85,1.75,1.75,1.15,1.1,1.1,1.05;"HTECH1",2.8 8,1.77,1.77,1.77,1.14,1.14,0.91,0.91,0.78,0.66,0.6 ,0.55;"HTECH2",4.6,2.25,2.25,2.25,1.43,1.43,1.28,1 .28,1.02,0.84,0.76,0.69;"HTECH3",6.66,2.74,2.74,2. 21,1.71,1.62,1.62,1.62,1.27,1.02,0.93,0.84;"HTECH4 ",8.05,3.11,3.11,2.5,2.04,2.04,1.98,1.98,1.51, 1.2, 1.09,0.98} with index for DMG1 = 14 (in range lookup)
MATCH is a column index
too many indexes :) but I'm an old idiot and mixed indexes :)
anyway problem is solved by last argument 0 (-1, 1) or defined (mandatory) place for last argument after comma

EDIT:
I still see 5 reps not 1000 :confused:

Quote:

Originally Posted by sandy666

I still see 5 reps not 1000 :confused:

I have to wait until if I have the power to do it, that is why I owe you them ( I.O.U ). Maybe as I am now in debt to you, you must charge me interest on them? - , like 10% every month from now on? So like in November I O U 1100 reps and then December I O U 1200 reps and so on. :) :cool:

Quote:

Originally Posted by sandy666

..
{"Part",13,25,37,48,73,101,145,201,2 89,600,750,1009;"DMG1",4.1,2.85,2.85,1.9,.... with index for DMG1 = 14 (in range lookup)
MATCH is a column index
too many indexes :) but I'm an old idiot and mixed indexes...

I think I see what you are saying its like “the 14th thing along” , like in if it was in a 1 dimensional or 1 “row” array,
This sort of thing
_number along{1 _ , 2 , 3 , 4 , 5 , 6 , 7 , 8 _ , 9 _ , 10_, 11_, 12_,13 , 14 __ , 15 , _16}
____INDEX({"Part",13,25,37,48,73,101,145,201,289,600,7 50,1009, "DMG1",4.1,2.85},1,14) = "DMG1"
Or similarly
__number along_____{1 _ , 2 , 3 , 4 , 5 , 6 , 7 , 8 _ , 9 _ , 10_, 11_, 12_,13 , 14 __ , 15 , _16}
__=MATCH("DMG1",{"Part",13,25,37,48,73,101,145,201,289,600,750,10 09, "DMG1",4.1,2.85},0) = 14
(Note I must change the row separating ; before the "DMG1" to a column separating , **

I also get indexes mixed up and also I mix up item numbers and indexes. Sometimes they are the same, sometimes they have a similar meaning

( ** just passing interest, if in a range we talk about the “number along” on any range, then we are talking about the range item number, and this “number along” goes like all columns in a row, then next row and so on.
So example in these 3 ranges, the range item number is 14

_Row\^Col A B C D E F G H I J K L M N

1 Part 13 25 37 48 73 101 145 201 289 600 750 1009 DMG1

Range("A1:N1").Item(14) = "DMG1"

_Row\^Col A B C D E F G H I J K L M

1 Part 13 25 37 48 73 101 145 201 289 600 750 1009

2 DMG1 $ 4.10 $ 2.85 $ 2.85 $ 1.90 $ 1.55 $ 1.35 $ 1.25 $ 0.95 $ 0.85 $ 0.75 $ 0.75 $ 0.65

Range("A1:M2").Item(14) = "DMG1"

_Row\^Col O P Q R S T U V W

4 Part 13 25 37 48 73 101 145 201

5 289 600 750 1009 DMG1 $ 4.10 $ 2.85 $ 2.85 $ 1.90

Range("O4:W5").Item(14) = "DMG1"

Here is a very interesting article by a very clever person on all that https://excelfox.com/forum/showthrea...column-looping )

Quote:

Originally Posted by sandy666

...anyway problem is solved by last argument 0 (-1, 1) ...

For Match is, I think, last argument 0 or 1 or -1
but for VLookUp, last argument is 0 or 1 ( or False or True )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Part	13	25	37	48	73	101	145	201	289	600	750	1009	DMG1

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Part	13	25	37	48	73	101	145	201	289	600	750	1009
2	DMG1	$ 4.10	$ 2.85	$ 2.85	$ 1.90	$ 1.55	$ 1.35	$ 1.25	$ 0.95	$ 0.85	$ 0.75	$ 0.75	$ 0.65

_Row\^Col	O	P	Q	R	S	T	U	V	W
4	Part	13	25	37	48	73	101	145	201
5	289	600	750	1009	DMG1	$ 4.10	$ 2.85	$ 2.85	$ 1.90

Give the chance to OP to start thinking himself :)

			Decorating Cost Each			Screen Costs Each			Cost Each Total			Total Cost For Quantity			Sell price each @ objective
Size	QTY	Apparel Cost EA	HTECH	FE	DMG	HTECH	FE	DMG	HTECH	FE	DMG	HTECH	FE	DMG	HTECH	FE	DMG
XS	10	$ 10.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 11.17	$ 11.75	$ 11.55	$ 111.71	$ 117.50	$ 115.50	$ 18.02	$ 18.95	$ 18.63
S	10	$ 10.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 11.17	$ 11.75	$ 11.55	$ 111.71	$ 117.50	$ 115.50	$ 18.02	$ 18.95	$ 18.63
M	10	$ 10.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 11.17	$ 11.75	$ 11.55	$ 111.71	$ 117.50	$ 115.50	$ 18.02	$ 18.95	$ 18.63
L	10	$ 10.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 11.17	$ 11.75	$ 11.55	$ 111.71	$ 117.50	$ 115.50	$ 18.02	$ 18.95	$ 18.63
XL	10	$ 10.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 11.17	$ 11.75	$ 11.55	$ 111.71	$ 117.50	$ 115.50	$ 18.02	$ 18.95	$ 18.63
2XL	10	$ 11.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 12.17	$ 12.75	$ 12.55	$ 121.71	$ 127.50	$ 125.50	$ 19.63	$ 20.56	$ 20.24
3XL	10	$ 12.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 13.17	$ 13.75	$ 13.55	$ 131.71	$ 137.50	$ 135.50	$ 21.24	$ 22.18	$ 21.85
4XL	10	$ 13.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 14.17	$ 14.75	$ 14.55	$ 141.71	$ 147.50	$ 145.50	$ 22.86	$ 23.79	$ 23.47
5XL	0	$ 14.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.35	$ -	$ 15.17	$ 15.75	$ 15.55	$ -	$ -	$ -	$ 24.47	$ 25.40	$ 25.08
6XL	0	$ 17.00	$ 1.14	$ 1.40	$ 1.55	$ 0.03	$ 0.03	$ -	$ 18.17	$ 18.43	$ 18.55	$ -	$ -	$ -	$ 29.31	$ 29.73	$ 29.92
	80											$ 953.70	$ 1,000.00	$ 984.00