• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index/Match or Vlookup returning #N/A's

B

boadacious

New Member
Hi, could anyone troubleshoot this formula?


=INDEX('Asset Allocations'!K16:K25, (MATCH('Risk Calculator'!E3, 'Asset Allocations'!A16:A25, 0)))


Also tried:


=VLOOKUP(E3, 'Asset Allocations'!A16:K25, 11)


I can't get away from NA errors - there is an exact match to the value in E3 in the first column of the table array used in the VLOOKUP, and a value in the last column for it to get.


E3 contains a value derived from a nested IF function (at the moment its 3, but changes depending on input earlier etc)


Every single cell involved in this formula has been formatted as number to two decimal places too!


Any help would be greatly appreciated.
 
Hi ,


I tried your first formula :


=INDEX('Asset Allocations'!K16:K25, (MATCH('Risk Calculator'!E3, 'Asset Allocations'!A16:A25, 0)))


and it returned a result on my computer ; no error result.


Can you enter the formula once more , and check ?


The only point can be about the exactness of the values in E3 and in the table ; even a difference in the 10th decimal will be a no match result. Place your cursor in E3 , press F2 and then F9 , and see whether you get the same value which is present in your table.


Narayan
 
Hi Narayan, thanks for the quick reply.


Same error as before upon trying it again.


Both the value in E3 and its equivalent it should look up are just 1 digit numbers, both identical, no decimals at all present.


EDIT: Ah! F2 then F9 Did find the correct value. However, i can't leave it in that state as i need the formula in E3 to be present.
 
Hi ,


After pressing F2 and F9 , and getting the value , press the Escape key to revert to the original formula.


Getting the value will allow you to cross-check whether the value you are trying to lookup and the value in the table are identical.


Can you copy + paste the formula from my post ( or your initial post , for that matter ! ) and check whether the quotes are a problem , or any extra spaces or ... ? When I do this on my computer , I don't get any error result !


Narayan
 
I got the value via F2 and F9, cross checked it, and it is correct.


I then copy + pasted the formula you posted, and there has been no change in result.


Thanks again for your continued assistance with this, its flummoxing not only me but other people in the office, so if you solve it, we shall have a round fo applause for you!
 
Hi ,


You say you have formulae in A16 through A25 ; Can you copy + paste them here ?


What about the table K16 through K25 ? Are these values or are there formulae in them also ?


I am sure if you can upload the worksheet , the matter can be resolved.


Narayan
 
Hi ,


The problem lies in your value in E3 , it's not really a numeric 3 !


Convert this to a value , using the VALUE function , and I think it should work.


Narayan
 
Hi ,


What gives this away is when you do F2 and F9 ; try using a formula like =VALUE(E3) in any unused cell , and placing your cursor on that cell , press F2 and F9 ; you should see a single digit highlighted value ; of course , if the value were something like 157 , you would see a three-digit highlight.


When you try the same with E3 itself , you see a long highlighted cursor , which means the value is not the number 3 , but a lot of text and then the digit 3.


Narayan
 
Ah i misunderstood what came up on the F2 then F9 - very useful to know! I'm trying to cobble up a good knowledge of excel, but i missed out alot of steps like this along the way, so little lessons like this are great for rounding out my knowledge.


Once again, thanks!
 
You must log in or register to reply here.
Back
Top