Forum Discussion
Solved
Lookup data in table and insert various columns into row in worksheet
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!
=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.
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 =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.
- Thanks very much. It worked fine!
- Show some sample data and desired output (attach a sample excel file).
