Excel inventory sheet

[jstewart1957]

We have a template to track inventory with products we buy and they have associated costs. I've created a table on a separate tab in the file with a list of products in one column and the associated cost in the adjacent column. On the main inventory sheet, I would like to be able to enter the product name and have the cost column populated with the cost from the table on the separate tab. The list of products is rather lengthy, so using IF statements would be awkward. What function/formula would work?

Thanks in advance,

Jimmy

 

[Fluff]

Hi & welcome to MrExcel.
Have a look at Xlookup How to use the Excel XLOOKUP function | Exceljet

or if you don't have that yet, Vlookup How to use the Excel VLOOKUP function | Exceljet

 

[jstewart1957]

Thank you for the response. I've looked at both, but don't seem to be able to get them to populate the inventory sheet they way I want them to.

 

[Fluff]

In that case can you post some sample data & expected results using the XL2BB add-in?

 

[jstewart1957]

Here is part of the inventory sheet that shows product which is populated from another tab as part of data validation. I have typed in the "unit value" costs to show what I'm expecting.

Inventory list-JS.xlsx
	D	E	F
3	Product	Description	Unit Value
4	Microchip (24 hour)	product	$12.00
5	ID bands (red)	supplies	$46.46
6	Esbilac Puppy	Formula
7	Da2pp	Vaccine	$3.35
8	Da2pp+lv4	Vaccine	$4.98
9	Da2pp	Vaccine	$3.35
Inventory List

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:I159	Expression	=$I4="Yes"	text	NO
B4:I159	Expression	=#REF!=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3	Any value
F3	Any value
D3	Any value
D4:D159	List	=Data!$A$2:$A$28

Here is my table on a separate tab that shows the product names and the costs associated with the product. I would like to be able, on the main page, to have the user select the product and have the unit cost automatically entered in the unit value column.

Inventory list-JS.xlsx
	A	B
1	Product	Unit Cost
2	1 ml syringes (box)	$24.00
3	1 ml syringes no needles
4	1ml Tuberculin syringes
5	24ml KMR Liquid (case)	$60.00
6	3 ml Syringes (box)
7	Da2pp	$3.35
8	Da2pp+lv4	$4.98
9	Doxy tabs 100mg bottle
10	doxycycline 100mg (bottle)
11	Esbilac Puppy
12	Fecatector bag (50)
13	Felv/Fiv Combo Witness Test (box)
14	FRVCP	$3.54
15	ID bands (red)	$46.46
16	Lg Exam Gloves (box)
17	Med Exam Gloves (Box)
18	Microchip (24 hour)	$12.00
19	Praziquantel susp. (Bottle)
20	Pyrantel 4oz
21	Rabies	$1.76
22	Suppli-Cal bottle
23	supplical
24	Witness HW test (Box)	$12.00
Data

Thank you

 

[Fluff]

Ok, thanks for that.
2 options in-case you don't yet have Xlookup.

+Fluff New.xlsm
	D	E	F	G
3	Product	Description	Unit Value
4	Microchip (24 hour)	product	12	12
5	ID bands (red)	supplies	46.46	46.46
6	Esbilac Puppy	Formula	0	0
7	Da2pp	Vaccine	3.35	3.35
8	Da2pp+lv4	Vaccine	4.98	4.98
9	Da2pp	Vaccine	3.35	3.35
Input

Cell Formulas
Range	Formula
F4:F9	F4	=XLOOKUP(D4,Data!$A$2:$A$24,Data!$B$2:$B$24,"",0)
G4:G9	G4	=VLOOKUP(D4,Data!A2:B24,2,0)

PS on your Inventory sheet the Da2pp had a space after it which isn't on the Data sheet