Using xlookup/vlookup formula into a validated cell to return a value into that cell

Hi all,

Could you please help? I'm trying to use a lookup formula to return results into a column that has validations (must be 11 digits).

I've used lookup for other validated columns returning results like email addresses and countries. These did not work at first when I selected table names/named ranges in the formula - validation error appeared and also '#spill' error. When I just selected the columns "long-hand", they worked. I can't seem to get the telephone numbers to return though? At first I found a problem with one phone number that had a space and a length check LEN formula seems to count space as a digit. Once I closed that up I thought it would work. Then I thought extra blank rows/fields in the table may be a problem so I got rid of blanks in the column of telephone numbers and it still doesn't work. Thoughts please?

Hi,
I refreshed the rule and it seems to work now.
Dont know what the problem.

Thanks for coming back to me Belinda. Hi there in Israel

What do you mean you by 'refreshed the rule'? I'm not aware of this function. I looked around and in the DATA menu there's a 'refresh all'. Tried that and I'm still getting validation error if trying to vlookup into the validated column. Please clarify what you did? Thank you!

Hi there in London

What I meant was that I removed the validation and applied the rule all over again, to "refresh" it.
I suspect that the formula was reading as a code and not producing the output of the code (see attached to show what I mean).

That's why it was determined as more than 11 characters.

Yes, exactly. Sorry I wasnt clear enough in the first time.

Good point regarding the cell formatting, I returned back to your initial file, and saw that indeed they were formatted as text (see print screen).
You should change it to number (not general). It works on your original source with the chnging of the format, I tested that as well.


If it still doesnt come through - please uploap again the latest file you are working on to inspect the reason for the failure.

Good Luck.

Just a thought...
If you format as number and put Apostrophe (')before it - it will show the number including leading zeros. I dont think that the apostrophe is counted as a character , it also isnt visible in the cell display.

Yes sure using a ' in front keeps leading zero.

In the end I think as it's a users' spreadsheet, not knowing what they'll do and perhaps lower likelihood of direct lookup into the sheet, I'll keep it TEXT formatted.

After submission, I can deal with getting the leading zeros in if any problem. Also a work-around is that they can look-up outside the sheet and just paste results into the columns which overrides validations anyway!

Thanks Belinda for engaging in my minutiae

Glad you found a compromise solution, and good luck!

Thank you Belinda!