"Vlookup unrepeated values"

[Marwan69]

Hello everyone,

I have a list of items and some of these items are repeated in every other 10 or so rows: For example:

size_sku​	product​	size​	ingredient_sku​	ingredient_name​
43​	Cappuccino​	Small​	sku_111​	Coffee Double/Single Shot​
43​	Cappuccino​	Small​	sku_115​	Kraft Ripple Paper Cup 8oz​
43​	Cappuccino​	Small​	sku_116​	Kraft Ripple Paper Cup 12oz​
43​	Cappuccino​	Small​	sku_117​	Black Plastic Lid 8oz​
43​	Cappuccino​	Small​	sku_118​	Black Plastic Lid 12oz​
43​	Cappuccino​	Small​	sku_124​	Brown Paper Napkin​
43​	Cappuccino​	Small​	sku_126​	2-Cup tray​
43​	Cappuccino​	Small​	sku_129​	Brown Wrapped Wooden Stirrer​
43​	Cappuccino​	Small​	sku_156​	Full Fat Milk Nadic​
43​	Cappuccino​	Small​	sku_172​	Sugar Satchet​
43​	Cappuccino​	Small​	sku_201​	Cocoa Alalali​
			Total​
44​	Cappuccino​	Medium​	sku_111​	Coffee Double/Single Shot​
44​	Cappuccino​	Medium​	sku_115​	Kraft Ripple Paper Cup 8oz​
44​	Cappuccino​	Medium​	sku_116​	Kraft Ripple Paper Cup 12oz​
44​	Cappuccino​	Medium​	sku_117​	Black Plastic Lid 8oz​
44​	Cappuccino​	Medium​	sku_118​	Black Plastic Lid 12oz​
44​	Cappuccino​	Medium​	sku_124​	Brown Paper Napkin​
44​	Cappuccino​	Medium​	sku_126​	2-Cup tray​
44​	Cappuccino​	Medium​	sku_129​	Brown Wrapped Wooden Stirrer​
44​	Cappuccino​	Medium​	sku_156​	Full Fat Milk Nadic​
44​	Cappuccino​	Medium​	sku_172​	Sugar Satchet​
44​	Cappuccino​	Medium​	sku_201​	Cocoa Alalali​
			Total​
45​	Macchiato​	Small​	sku_111​	Coffee Double/Single Shot​
45​	Macchiato​	Small​	sku_115​	Kraft Ripple Paper Cup 8oz​
45​	Macchiato​	Small​	sku_116​	Kraft Ripple Paper Cup 12oz​
45​	Macchiato​	Small​	sku_117​	Black Plastic Lid 8oz​
45​	Macchiato​	Small​	sku_118​	Black Plastic Lid 12oz​
45​	Macchiato​	Small​	sku_124​	Brown Paper Napkin​
45​	Macchiato​	Small​	sku_126​	2-Cup tray​
45​	Macchiato​	Small​	sku_129​	Brown Wrapped Wooden Stirrer​
45​	Macchiato​	Small​	sku_156​	Full Fat Milk Nadic​
45​	Macchiato​	Small​	sku_172​	Sugar Satchet​
			Total​
46​	Macchiato​	Medium​	sku_111​	Coffee Double/Single Shot​
46​	Macchiato​	Medium​	sku_115​	Kraft Ripple Paper Cup 8oz​
46​	Macchiato​	Medium​	sku_116​	Kraft Ripple Paper Cup 12oz​
46​	Macchiato​	Medium​	sku_117​	Black Plastic Lid 8oz​
46​	Macchiato​	Medium​	sku_118​	Black Plastic Lid 12oz​
46​	Macchiato​	Medium​	sku_124​	Brown Paper Napkin​
46​	Macchiato​	Medium​	sku_126​	2-Cup tray​
46​	Macchiato​	Medium​	sku_129​	Brown Wrapped Wooden Stirrer​
46​	Macchiato​	Medium​	sku_156​	Full Fat Milk Nadic​
46​	Macchiato​	Medium​	sku_172​	Sugar Satchet​
			Total​
47​	Espresso/Double​	Small​	sku_110​	Brown Kraft Paper 4oz​
47​	Espresso/Double​	Small​	sku_111​	Coffee Double/Single Shot​
47​	Espresso/Double​	Small​	sku_124​	Brown Paper Napkin​
			Total​

So, I want "Vlookup" function to pick up only one of ie: "Coffee Double/Single Shot" and not all the "Coffee Double/Single Shot". It doesn't matter which one to pick but most importantly
one of this item only "Coffee Double/Single Shot". How to do that?

Many thanks,
Marwan

 

[Marwan69]

This is what it returned: #N/A
Also I'd like to mention that if you look at the table above you'll find: Coffee Double/Single Shot with ingredient 18g and 36g. How can I return the value with only 18g?
Thanks.

 

[Marwan69]

This is the table that I want the value to be returned in:

Products Recipe-Final.xlsx
	I	J
1	inventory_items
2	Brown Kraft Paper 4oz	#N/A
3	Coffee Double/Single Shot	#N/A
4	Black Lid With Red Heart 12oz	#N/A
5	Kraft Ripple Paper Cup 8oz	#N/A
6	Kraft Ripple Paper Cup 12oz	#N/A
7	Black Plastic Lid 8oz	#N/A
8	Black Plastic Lid 12oz	#N/A
9	Black Wrapped Straight Straw	#N/A
10	Black Clip Cab	#N/A
11	Black mask 3ply with earloop	#N/A
12	Black Vinyl Glove - Powder-Free L	#N/A
13	Brown Paper Bag with Flat Handle	#N/A
14	Brown Paper Napkin	#N/A
15	Sticker	#N/A
16	2-Cup tray	#N/A
17	Brwon Kraft Paper Box with Hinged Lid 25oz	#N/A
18	Brwon Kraft Paper Box with Hinged Lid 40oz	#N/A
19	Brown Wrapped Wooden Stirrer	#N/A
20	LeDari Black Knife	#N/A
21	Pistachio Cream (OVVIO)	#N/A
22	Monin Pomegranate syrup	#N/A
23	Monin White Chocolate	#N/A
Items Vs. Recipe (2)

Cell Formulas
Range	Formula
J2:J23	J2	=VLOOKUP(I2,'Products Recipes (2)'!$A$7:$H$1143,8,FALSE)

 

[Joe4]

I think you have a problem with your range reference of the range you are looking up into. You have it looking in column A-H.
But the first column in your range reference should be the range you are matching on.
In your earlier examply, it was column E, not column A that had the ingerdient_names.
If that is still the case, then your formula needs to look like:
=VLOOKUP(I2,'Products Recipes (2)'!$E$7:$H$1143,4,FALSE)

 

[navinrb]

This is the table that I want the value to be returned in:

Products Recipe-Final.xlsx
	I	J
1	inventory_items
2	Brown Kraft Paper 4oz	#N/A
3	Coffee Double/Single Shot	#N/A
4	Black Lid With Red Heart 12oz	#N/A
5	Kraft Ripple Paper Cup 8oz	#N/A
6	Kraft Ripple Paper Cup 12oz	#N/A
7	Black Plastic Lid 8oz	#N/A
8	Black Plastic Lid 12oz	#N/A
9	Black Wrapped Straight Straw	#N/A
10	Black Clip Cab	#N/A
11	Black mask 3ply with earloop	#N/A
12	Black Vinyl Glove - Powder-Free L	#N/A
13	Brown Paper Bag with Flat Handle	#N/A
14	Brown Paper Napkin	#N/A
15	Sticker	#N/A
16	2-Cup tray	#N/A
17	Brwon Kraft Paper Box with Hinged Lid 25oz	#N/A
18	Brwon Kraft Paper Box with Hinged Lid 40oz	#N/A
19	Brown Wrapped Wooden Stirrer	#N/A
20	LeDari Black Knife	#N/A
21	Pistachio Cream (OVVIO)	#N/A
22	Monin Pomegranate syrup	#N/A
23	Monin White Chocolate	#N/A
Items Vs. Recipe (2)

Cell Formulas
Range	Formula
J2:J23	J2	=VLOOKUP(I2,'Products Recipes (2)'!$A$7:$H$1143,8,FALSE)

Yeah Mr Joe4 is right you have entered the wrong column number.
instead of 8 replace with the 4 . It should work fine.
Remember always count the column index no starting from the range where your lookup value is located.

 

[Marwan69]

Excellent guys! Worked perfectly. Many thanks

 

[Joe4]

You are welcome. Glad were able to help!

 

[Marwan69]

As I mentioned it worked perfect however, I encountered the issue that you Mr. Joe4 stated about:"VLOOKUP will only pick up the first one it finds".
I have different weights for some items such as: Coffee Double/Single Shot. As shown on the table on previous page. There's 18g and 36g for the same irem ie: Coffee Double/Single Shot
with different costs. How to overcome this and let Vlookup return to me one of the cost as per my desire.

Many thanks.

 

[Joe4]

If you have two values you want to match on, it gets a little trickier, as you can only match on one with VLOOKUP. However, you may be able to create a Helper column and use VLOOKUP as shown here:

Excel formula: VLOOKUP with multiple critiera | Exceljet

The VLOOKUP function does not handle multiple criteria natively.

exceljet.net

.

Alternatively, you can also use INDEX/MATCH, like this: Excel formula: INDEX and MATCH with multiple criteria | Exceljet

 

[Marwan69]

Thanks. Seems a lot of work but i'll give it a shot!
Cheers.