VLookip with Data Validation

Hi,

Please can anyone help me?

I'm looking to have a data validation list in 'G18' showing a list of Valve Block Types based on the Manufacturer 'G17'.

The list of Valve Block Types are detailed in DVI sheet (I13:Q45)

File attached.


Kind Regards,

Like this?

Data validation + Array formula (Ctrl+Shift+Enter)
=IFERROR(INDEX($K$14:$K$113,SMALL(IF($J$14:$J$113=$G$18,ROW($J$14:$J$113)-ROW($J$13)+1),ROWS($J$13:J13))),"")

Hi AliGW

i am looking for for example, if Cell G17 shows Blain then G18 will show in the dropdown list only the items which contain Blain or if it shows Bucher then it only shows them items. .

Let me know if you need a screenshot.


thanks,

Hi Paul.

that is perfect. thank you

What you are looking to do is called "dependent drop down lists" or "dependent data validation lists".

https://www.excelcampus.com/tables/d...nt-drop-lists/
This is what I based everything off of.

I created a new worksheet in your workbook to house all of the DV tables. I then copied and pasted the data in columns J and K from your 'DVI' sheet to the new sheet, removed duplicates, and sorted based on manufacturer. It seems like there are some duplicates in there that you will have to manually remove (e.g. "230vac" vs "230 vac").

I used the following DV formulas:
G17 =INDIRECT("Table1[Manufacturer]")
G18 =OFFSET(Lists!$D$1,MATCH(G17,Lists!$D:$D,0)-1,1,COUNTIF(Lists!$D:$D,G17),1)

Follow the steps listed in the link above and you will see that you now have a dynamic, dependent drop-down list in G18.

When you select a manufacturer in G17, the available options in G18 will change accordingly.

See attachment for clarification.