Forum Discussion

Fiona11's avatar
Fiona11
Copper Contributor
Jan 16, 2020

Vlookup

I have created a Vlookup formula - VLOOKUP(A5,ETB!$A$9:$L$105,12), which returns the correct value, but when I copy it down it correctly becomes VLOOKUP(A6,ETB!$A$9:$L$105,12), but then returns the s...
excel
Formulas and Functions
Fiona11's avatar
Fiona11
Copper Contributor
Jan 16, 2020

Jakal_Moon 

Thanks so much for this - true just returns the same value as before and a comma gives me #N/A again.

 

I can't help thinking its something really basic that I'm missing, but I'm tearing my hair out trying to think of more things to try!

PReagan's avatar
PReagan
Bronze Contributor
Jan 16, 2020

Fiona11 

 

The #N/A suggests that your lookup value (A6) does not exist in the source data (ETB!A9:A105). Ensure that your lookup value exists in the list (the exact same value).

 

As a note Jakal_Moon, I have found it as a safe practice to always include "FALSE" as the [range_lookup] to ensure that VLOOKUP() returns the exact result that I am looking for (and otherwise an error if the lookup value does not exist).

  • Fiona11's avatar
    Fiona11
    Copper Contributor
    Jan 16, 2020

    No, it really does - they all do - I just pasted col A from the first sheet onto the ETB one. But even if I put the absolute value in the formula it still picks up the value on the first row.

     

     

    • PReagan's avatar
      PReagan
      Bronze Contributor
      Jan 16, 2020
      Would you mind sending a sample file showing this error? (Please of course remove any sensitive information)
      • Fiona11's avatar
        Fiona11
        Copper Contributor
        Jan 21, 2020

        Many thanks

         

        I've highlighted the culprits and the correct one (in Green)

         

        Good luck!

Resources