Text & Scientific Formated Cells + VLOOKUP

[GeeBDee]

Hi all

I have a spreadsheet that requires a reference number (SDAC REF) to be input which then triggers the VLOOKUP for the SDAC Name & STW Catchment. This all works fine until a reference number with (????E1??) is input, this then changes the cell to Scientific and the VLOOKUP does not work. I have tried formatting the reference cells to TEXT and this works well, however reference number that do not have letters in them will no longer trigger the VLOOKUP? So, is there anyway the reference cell can stop changing (????E1??) to scientific?

My VLOOKUP example for the SDAC Name is as below:

=IF(ISNA(VLOOKUP(H8,'SDAC +'!$A$3:$V$4887,3,FALSE)),"",VLOOKUP(H8,'SDAC +'!$A$3:$V$4887,3,FALSE))

I do not post very often so if I have missed something or done something wrong then I apologise.

GD

 

[joeu2004]

GeeBDee wrote: "however reference number that do not have letters in them will no longer trigger the VLOOKUP".

The best solution is to consistently treat SDAC REFs as text, not numbers, both in the lookup-value cell (H8) and the lookup column (A3:A4887).

As for avoiding "...E1..." as a number in scientific notation, that depends on how you input the SDAC REFs, in the first place. Are they typed manually? If not, how are they "input"?

But generally, it is unavoidable, unless it is typed manually, and you can tell the typist to prefix the SDAC REF with an apostrophe (single-quote).

 

[GeeBDee]

Hi Joeu
Thank you for the quick reply. The SDAC REF numbers are manually keyed in which then trigger the VLOOKUP. I have just tried formatting the SDAC Ref to text (H8) and the whole VLOOKUP sheet to text as well, this then works with the references that have E1 but the VLOOKUP then stops working when only numbers are keyed in. Is there no way round this?

GD

 

[GeeBDee]

Hi Joeu

Thank you for your help, I will go with the Apostrophe solution.

Many thanks again

GD