Jun 28 2020 10:43 PM
I am setting up a budget which I obtained from Nabid Mourad and when trying to use vlookuo to view month name for each month, I get an error '#REF#'. As per Nabid instruction, I have created a 'mylist' table array to indicate the months but cannot get the formula to work - help!!
Jun 28 2020 10:51 PM - edited Jun 29 2020 12:07 AM
@jeffjm Would be helpful if you could show the file you are working on and the formula that is returning the #REF! error.
Edit: @jeffjm I believe I found the video here:
https://www.youtube.com/watch?v=5ZCBUgkVqqQ
Two minutes into the video, he describes how to create a named range. Ten minutes into the video, he is describing how to create the VLOOKUP function, using the named range as the 'table_array'.
A #REF! error occurs when you try to call for a column index that is beyond the width of the table_array.
Check that the named range "MyList" is indeed 2 columns wide and that the VLOOKUP function calls for the 2nd column.
Jun 29 2020 12:27 AM
If you mean Nabil Mourad why don't ask @nabilmourad here.
Jun 29 2020 05:13 AM
Hi,
@jeffjm I am not sure what problem you have unless I see the file.
@Riny_van_Eekelen this is a beginners' tutorial. That explains why the pace is slow for an advanced user ... When you create Public Tutorials you realize that you cannot make everyone happy (although I wish to). But anyway, I respect your opinion.
For the Named range I created, that's a Static named range, so it's normal to get a #REF when providing a Col_Index_number outside the range). There are other more advanced methods for creating a Floating named range, and @Sergei Baklan (the Excel guru) can confirm that.
Jun 29 2020 05:33 AM
@nabilmourad I had no problem with the pace. Just mentioned the time stamps so that the creation of "MyList" and the actual use of it could easily be traced.
Jun 29 2020 04:25 PM
@Riny_van_Eekelen Please put me in touch with Nabil Mourad so I can chat with him about the pivot table and formula.
Jun 29 2020 09:39 PM
@jeffjm You can respond to the post he made earlier or send him a private message. Hover the mouse pointer over his name tag and click "Message"