VLOOKUP using cell reference with IMPORTRANGE gives error

Question

I can't see what I'm doing wrong with this.

This Doesn't Work

=Vlookup(L2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","responses!B2:R8"),4,0)

I get this error:

Did not find value '102' in VLOOKUP evaluation.

This Does Work

=Vlookup(102,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","responses!B2:R8"),4,0)

I get the correct value.

The only difference is the first version (not working) uses a cell reference L2 in the VLOOKUP. The second version (working) uses a static value 102 in the VLOOKUP.

I've tried wrapping in " and ' but neither work.

Can someone please help me fix this so it works with a cell name and explains why my solution isn't working?

Answer 1

VLOOKUP is format sensitive. It neither 'matches' text to number nor number to text, even where on screen the appearance is identical. This is a standard gotcha, though unfortunately not mentioned here. (Another is 'trailing spaces', specially NBSP, which can complicate diagnosis.)

However the data (whether the search_key or part of the range) need not be reformatted for VLOOKUP to work as the 'conversion' may be done 'on the fly'.

For example, converting a text search_key to numeric format can be achieved by prepending -- (the double unary), or by coercion with 1* (multipying by 1) within a formula.

In OP's case this might mean replacing L2 with --L2 or 1*L2.

Answer 2

B,

A better way to resolve your problem might be to wrap the formula you are putting into M2 in an IFERROR() formula. & have the first parameter coerced as text by appending an empty space character to it i.e. L2&"" & the second parameter coerced as a number i.e. L2*1 that why it won't matter how the user inputs the information it will still evaluate the VLookUp() properly.

So your formula =Vlookup(L2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","responses!B2:R8"),4,0)

Would now instead be: IFERROR(Vlookup(L2&"",IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","responses!B2:R8"),4,0),Vlookup(L2*1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","responses!B2:R8"),4,0))

Using Arrays you won't have to copy and paste your formula each time a new user fills out your form So if you change your VLookUp() search_key from L2 to L2:L and wrap your entire VLookUp() in an ARRAYFORMULA() it will allow the formula to propogate downward as needed.

This would make your formula =IFERROR(ARRAYFORMULA(Vlookup(L2:L&"",IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","responses!B2:R8"),4,0)),ARRAYFORMULA(Vlookup(L2:L*1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","responses!B2:R8"),4,0)))

Good Luck -Xzila

Answer 3

your L2 cell is formatted (evaluated) as "text" which results in #N/A

to resolve it you have 2 options:

• select cell L2 and go to Format > Number > Number

  

• wrap cell L2 into VALUE() which will convert it into numeric value eg. use this formula:

  =VLOOKUP(VALUE(L2),
   IMPORTRANGE("1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs",
               "responses!B2:R8"), 4, 0)