Using VLOOKUP when Data Validation is Applied

[HangMan]

Is there any way to use a VLOOKUP on a column which already has Data Validation applied. If I try this, even though the lookup values match the data validation values 100% it always errors and I end up having to remove the data validation prior to doing the lookup and then re-applying it afterwards which is a bit of a pain.

Anyone know a way around this?

Many thanks

[XOR LX]

Hi.

In what way does it error? What precisely is the data validation and to which column(s) does it apply?

Perhaps this is a case where seeing an actual workbook would be of benefit.

Regards

[Tony Valko]

Common problems when trying to do lookups:

http://contextures.com/xlFunctions02.html#Trouble

[HangMan]

I want to use a VLOOKUP to populate column D using the data from Sheet 2. Column D uses Data Validation.

The issue is that if the 'LOOKUP' value in column A doesn't appear on Sheet2, then the VLOOKUP errors.

So in this example, '1' is not available in column A on Sheet2, therefore the lookup, =IFERROR(VLOOKUP($A2,Sheet2!$A$2:$D$23,4,FALSE),"") errors.

However if I started the lookup in row 3, because '2' is available on Sheet2, =IFERROR(VLOOKUP($A3,Sheet2!$A$2:$D$23,4,FALSE),"") works.

Therefore the question is, how can I use a VLOOKUP in this instance so I don't get the error?

[Czeslaw]

If properly understood, can they?

[HangMan]

Um, okay, so that works, but why not a VLOOKUP?

[Czeslaw]

And I think that the work both of the same formula. I give the check formula.

[HangMan]

Strangely both work on the sample sheet, but neither on my main sheet (which I can't upload), so really not sure what is going on there...