ExceL Formula Failing

Cotta

Hi All,

The Vlookup below is failing and it doesn't make any sense. I'm working off a static template and I need to use the value in "D30" as a lookup against a value within "tab2," however this is resulting in an error message (#N/A), even though the corresponding value is within "tab2". If I change the lookup value to "C30" the formula works fine, however I don't wish to use the value within "C30" and I can't change any locations within the "template" tab, how do I resolve this?

=VLOOKUP(VLOOKUP(D30,'tab2'!A:JU,MATCH(Template!I29,'tab2'!$1:$1,0),0),Sheet2!D:E,2,0)

Le_Kirk

We don't know what is in D30 or C30. Could it be approximate/exact matching? Try this link for some help. I find if you can sort it yourself, you remember it better next time. Also breaking down the formula into manageable chunks (as previously advised) always helps find the error.

AndyPix

I cant help noticing you ask an awful lot of excel questions ..


Would it not be worth asking your employer for an advanced excel course if you are expected to do this kind of stuff for your job ?
Rather than cobbling together solutions from forums ?


Quite happy to help if i can, but it seems unfair that you have to resort to this kind of support


Andy

Rubidium

What is the format of cells C30 and D30?

Cotta

Cell D30 contains a "general" reference number namely "GH12".


Cell E30 contains a "general" number namely 000333444.

Ainsley1

We are a disadvantage being remote from the problem, it is very possible that there is nothing wrong with your formula but the matching required in the lookup cannot be located hence #n/a is returned.

Why not take the advice posted on your threads and split up your formulae into separate cells linked together and use named ranges that will help us all to understand your sheets better....and will show you which bit of your complex formula is giving you the answer you do not expect....

In this instance to prove it is the cell contents (d30 or c30) try changing them to something you think ought to be located....and then if it still cannot be found copy that destination cell contents and paste-value into cell d30. That might help you track down the problem.

Also are you sorting the table(s) being looked up? Note: depending on the type of lookup specified there may well be problems if the data table is not sorted see, for example, explanations on one of the training sites exceljet that can be returned from a Google search.......
....oh did somebody already mention training?:T

Chino

Does this provide any food for thought?
https://support.office.com/en-us/article/How-to-correct-a-N-A-error-in-the-VLOOKUP-function-e037d763-ffc3-4fae-a909-89c482d389b2?ui=en-US&rs=en-US&ad=US