Vlookup, Index Match both returning #N/A value not available error

Hi guys

I have 2 worksheets in my excel file, the All standards worksheet has a large table of items referenced as AllStandards in it and the Doable sheet has a smaller table of selected data referenced as Doable. In the Doable sheet I was trying to create a system where I can input a specific ID number in the B column and in the C column, a vlookup or index match formula would search over in the All Standards sheet and get the description relating to that ID number. I tried both the formula methods but I keep getting returned a #N/A error as you'll be able to see in the C2 and C3 cell in the Doable sheet. Could someone please help me with this? I have no idea what I'm doing wrong. Thanks

This works okay for me in C2:

=VLOOKUP([@Number],AllStandards[[Number]:[Description]],2,0)

Hope this helps.

Pete

Hi Ali,
Thanks for the welcome. I'm so glad I found this place. Now I can solve all my excel woes
Thanks for the solution, it really helps. I just had a few questions about it just to get my head around using these formulas. For the Index function, the first value is an array so would it be alright to just use the whole table reference of AllStandards and change the column num to 3 or do I have to specifically stick with AllStandards[Description]? The other question in regards to the match function is fairly similar in that excel says the second value of the match function is a lookup_array so I was just wondering why my formula of using the whole AllStandards table as a reference didn't work? Is it just one of the limitations of the function that you have to stick to one column or row?

Thanks a lot

Hi Pete,
Thanks a lot for the solution. This helps so much in what I'm trying to achieve. I just had a few questions about vlookup and I was hoping you could answer them. Excel says the second value of the vlookup formula is a table_array so why doesn't it work when I put in just AllStandards because to the best of my understanding that is a table and an array?

Thanks

With VLOOKUP the lookup values have to be in the first column of the table defined in the second parameter, so in your case that means you want to look up the Number column and not the NZQA column (which is the first column of the AllStandards table). The third parameter defines the column that you want to return the matching data from, and this is relative to the lookup column.

Check out my formula for the actual syntax.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete