Lookup data in table and insert various columns into row in worksheet

[Jeffjeff1945]

I want to have a dropdown list from a table created from a supplier that shows in a row with a description but also an item number and a rate in various columns in the table.

I can get the description "data Validation"

but cant see how to get the other 2 from the table. I've tried VLOOKUP & HLOOKUP and XLOOKUP has me confused.

Can someone help me please!

[#2]

Show some sample data and desired output (attach a sample excel file).

[#3]

@Jeffjeff1945 

I have the table in worksheet "Current Support Items". I am working in worksheet "Quote"

The table has several columns

Support Item Number	Support Item Name	Registration Group Number	Registration Group Name	Support Category Number	Support Category Name	Unit	Quote	WA
01_002_0107_1_1	Assistance With Self-Care Activities - Standard - Weekday Night	0107	Daily Personal Activities	1	Assistance with Daily Life (Includes SIL)	H	No	$69.77
01_002_0107_1_1_T	Assistance With Self-Care Activities - Standard - Weekday Night - TTP	0107	Daily Personal Activities	1	Assistance with Daily Life (Includes SIL)	H	No	$71.86
01_003_0107_1_1	Assistance From Live-In Carer	0107	Daily Personal Activities	1	Assistance with Daily Life (Includes SIL)	H	Yes
01_004_0107_1_1	Assistance with Personal Domestic Activities	0107	Daily Personal Activities	1	Assistance with Daily Life (Includes SIL)	H	No	$52.73
01_010_0107_1_1	Assistance With Self-Care Activities - Night-Time Sleepover	0107	Daily Personal Activities	1	Assistance with Daily Life (Includes SIL)	E	No	$262.16
01_011_0107_1_1	Assistance With Self-Care Activities - Standard - Weekday Daytime	0107	Daily Personal Activities	1	Assistance with Daily Life (Includes SIL)	H	No	$62.17
01_011_0107_1_1_T	Assistance With Self-Care Activities - Standard - Weekday Daytime - TTP	0107	Daily Personal Activities	1	Assistance with Daily Life (Includes SIL)	H	No	$64.04

I have named the columns in the table as; "Item Number" "Description" and "Rate". There is over 200 rows in the table all with different Item numbers but some duplicated Descriptions.

My quote worksheet is under and you can see how I have accessed via Data Validation, different descriptions but I need to input the Item Number and the corresponding Rate for each, from the table

Hope you can help.

ITEM NUMBER	DESCRIPTION	MO	TU	WE	TH	FR	SA	SU	TOTAL DAYS	RATE
PUBLIC HOLIDAYS included in period of support requested
=XLOOKUP(B26,'Current Support Items'!B2:B139,'Current Support Items'!A2:A139)	Assistance With Self-Care Activities - Standard - Public Holiday
Scheduled Support
	Assistance With Self-Care Activities - Night-Time Sleepover
	Assistance With Self-Care Activities - Standard - Weekday Daytime - TTP

[#4 · Accepted solution]

@Jeffjeff1945 

=IFERROR(VLOOKUP(B2,'Current Support Items'!$B$2:$I$8,8,FALSE),"value not found in column support item name")

You can try this formula in cell K2.

=IFERROR(INDEX('Current Support Items'!$A$2:$A$8,MATCH(Quote!B2,'Current Support Items'!$B$2:$B$8,0)),"value not found in column support item name")

You can try this formula in cell A2.

[#5]

Thanks very much. It worked fine!