Forum Discussion
Excel Vlookup / Index-Match Not Working
Hi all, Thanks in advance for your help. I am having a problem trying to find a way to get Vloookup or Index/Match to return a value because my Lookup Value is a reference cell with an Index (I assume that is what is throwing this off). My Lookup Value is cell C4 and in C4 is {=INDEX(calender,ndx+0)} because it is part of an active calendar.
I have a calendar on sheet 2 (Referenced Above) that i am trying to enter data from sheet 1 into based on the day of the month (Cell C4) and corresponding days that a budget item is due (Array) . I have attached info for the way the formulas are written currently for review and I would like to note that if I change the VLookup Range Lookup to TRUE it will return data from the second column in the array but relative to the last value in column 1 of the array, no matter what the Lookup Value references it always returns column 2 of the last row in column 1 in the Array, like it doesn't care what value is in C4.
Is there a way to make the VLookup or Index/Match work if the Lookup Value is a result of a formula (Specifically, another INDEX)?
3 Replies
Gatertech I believe I recognise the template and you are trying to use this very basic (and quite useless) one to do something it isn't built to do. Not that you can't amend it, but it will not be very easy in most cases. Difficult to give recommendations on the basis of screenshots. Can you upload your file? Just delete anything private and confidential but leave the basic information from Sheet1 in there.
Riny_van_Eekelen Thanks Riny, Yes this is a very basic sheet however I am trying to understand the concept and what i am doing wrong here. I have attached the file, thank you for the help.
Gatertech This particular template (I found it in the Excel start-up page) is nothing more than something that produces a month view, where you can then type in whatever is happening on a certain day and perhaps print it as a reference document. The date fields in the calendar are in fact date values where the due dates on your Sheet1 are just numbers. I adopted the calendar template for you and inserted VLOOKUP formulae that display the Description and Amount for that particular day. However, it will not work if you enter multiple items on the same day.
Having said that, a calendar template is NOT suitable to control a budget. You can find other templates to do a better job. But, you must stick to whatever the template dictates as they are often very difficult to customise. Up to you to decide though.
