1

I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.

My vlookup formula is 

=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)

Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?

Improve this question
4
  • 1
    It shouldn't make a difference whether the cell targeted by the VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?
    – bdr9
    Jul 21, 2014 at 1:28
  • Since you set the fourth argument to FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.
    – ben
    Jul 21, 2014 at 12:01
  • If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is
    – datatoo
    Jul 23, 2014 at 20:43
  • @user348257 better share some sample data along with the MID formula you have been trying so far, help us to fix it!
    – Rajesh Sinha
    Oct 26, 2020 at 5:17

2 Answers 2

Reset to default
1

To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.

So, if you expect a number, you can just multiply the result by 1:

for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .

If you do the same inside your vlookup -->

Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)

you'll assure that both your values are numbers.

It should work.

Improve this answer
0

An alternative that does not need to expect either numerical or textual data but rather deals with either:

=IFERROR(VLOOKUP(C1,A1:B3,2,FALSE),VLOOKUP(TEXT(C1,"@"),A1:B3,2,FALSE))

It does the lookup "naturally" (with C1's contents as is), and if that fails, tries looking for a text version of C1's contents. Naturally, it might not be present in the data so an error amy result anyway, but it does check for both possibilities... if it has to.

Sorry, used simple references. The question is five years old, so it's really more for others who would have to adjust for whatever references any situation they apply it to needs.

Also, as noted by others, Excel's formulas always deal with the VALUE in a cell, so a constant if present, or the result of a formula, Named Range, etc., if such is present. Except for obvious exceptions like FORMULATEXT() or TYPE() and similar things. So formula vs. constant in cell was never an issue.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .