Vlookup and Match problem

**bobkap12** · 09-29-2022, 04:21 AM

**DocAElstein** · 09-29-2022, 02:08 PM

Hello bobkap12 , Welcome to ExcelFox

Originally Posted by bobkap12

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

????? –
I think maybe you mean C6 ?

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

**sandy666** · 09-30-2022, 02:40 AM

Code:

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

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

**DocAElstein** · 09-30-2022, 12:16 PM

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

I don’t understand?

I thought this was table bk ?
https://postimg.cc/mthjqfLQ https://i.postimg.cc/Hs9NrdFP/bk-Table-data-A1-M13.jpg

What am I missing?

**sandy666** · 09-30-2022, 01:44 PM

=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

**DocAElstein** · 10-01-2022, 01:20 PM

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/15VpN7Hj/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

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

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? )

**sandy666** · 10-01-2022, 07:32 PM

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

**DocAElstein** · 10-02-2022, 03:46 PM

Originally Posted by sandy666

I still see 5 reps not 1000

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.

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 )

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 )