0

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.

Error image

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.

Working image

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?

enter image description here

  • @pnuts, thanks for catching that. I corrected the typo between the 4 and 5. – Mr. B Aug 1 '17 at 15:17
  • @pnuts, sorry about the poorly worded question. I'm running on fumes... I've updated the question in an attempt to clarify. Basically I receive an error when using "cell reference" and I get the correct value when I use a static number for the reference. – Mr. B Aug 1 '17 at 15:42
  • I just tested it. the cell L2 has "text" 102 in it and the vLookup cell has the "number" 102 in it. This populates "L2" =arrayformula(SUBSTITUTE(K2:K,"#","")) so I guess I need to figure out how to make this return a number instead of text? – Mr. B Aug 1 '17 at 15:55
  • That worked! It converted it to a number. Will that always convert to number or only when paired with SUBSTITUTE? – Mr. B Aug 1 '17 at 16:17
  • Hey Mr. B, If I have answered your question to your satisfaction please select my response as the answer to your question. Thanks. – Xzila Jan 3 '18 at 21:11
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.

1

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

0

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

    1

  • 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)

    5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.