Forum Discussion

Copper Contributor

Dec 08, 2020

If and vlookup in Data Validation

Hello All, I have a excel where in i want the users to select from dropdown only for one specific number (ex 17) apart from that any number they select it should do vlookup from sheet2. Below is th...

excel

Formulas and Functions

Nagaraj007

Copper Contributor

Dec 09, 2020

Nagaraj007

Any one could help me, its very urgent

Riny_van_Eekelen

Platinum Contributor

Dec 09, 2020

Nagaraj007 Begin by uploading your file (remove any private and confidential information), rather than cut and paste a table that doesn't seem to represent your actual file.

Nagaraj007
Copper Contributor
Dec 11, 2020
Hi Riny_van_Eekelen ,

Please find the below file link.
https://www.transfernow.net/X5TTom122020
- Riny_van_Eekelen
  Platinum Contributor
  Dec 11, 2020
  Nagaraj007 Thanks! Unless I'm mistaken, you can not define a single data validation rule, returning a list for one particular value and a VLOOKUP for all others. If it can be done, I haven't yet discovered it.
  In stead, you would define named ranges for the values "_11" through "_17", each listing the possible options to choose from, even though 11 to 16 would only have one option. Use INDIRECT to point to the named ranges as the source for your lists. It would look something like this: =INDIRECT("_"&C3). The underscore is needed as named ranges may not start with a number.