SOLVED

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

New Contributor

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!

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

@Jeffjeff1945 

 

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

The table has several columns

 

 

Support Item NumberSupport Item Name

Registration Group

Number

Registration Group NameSupport Category NumberSupport Category NameUnitQuoteWA
01_002_0107_1_1Assistance With Self-Care Activities - Standard - Weekday Night0107

Daily

Personal Activities

1Assistance with Daily Life (Includes SIL)HNo$69.77
01_002_0107_1_1_TAssistance With Self-Care Activities - Standard - Weekday Night - TTP0107Daily Personal Activities1Assistance with Daily Life (Includes SIL)HNo$71.86
01_003_0107_1_1Assistance From Live-In Carer0107Daily Personal Activities1Assistance with Daily Life (Includes SIL)HYes 
01_004_0107_1_1Assistance with Personal Domestic Activities0107Daily Personal Activities1Assistance with Daily Life (Includes SIL)HNo$52.73
01_010_0107_1_1Assistance With Self-Care Activities - Night-Time Sleepover0107Daily Personal Activities1Assistance with Daily Life (Includes SIL)ENo$262.16
01_011_0107_1_1Assistance With Self-Care Activities - Standard - Weekday Daytime0107Daily Personal Activities1Assistance with Daily Life (Includes SIL)HNo$62.17
01_011_0107_1_1_TAssistance With Self-Care Activities - Standard - Weekday Daytime - TTP0107Daily Personal Activities1Assistance with Daily Life (Includes SIL)HNo$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 NUMBERDESCRIPTIONMOTUWETHFRSASUTOTAL DAYSRATE
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         
           
           
           
           
           
           
best response confirmed by Jeffjeff1945 (New Contributor)
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.

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

Thanks very much. It worked fine!