Vlookup help

Occasional Visitor

I have a spreadsheet where I need to lookup a value based on a drop down menu that changes for the period and year.


Variance Tab


Data Tab


I am trying to get a return value of ($23,312.78) on the Variance Tab in cell D4.


This is the formula that I have:  =IF(ISERROR(VLOOKUP(B3&C3&D2,Data,4,FALSE)),0,VLOOKUP(B3&C3&D3,Data,4,FALSE))

B3 is the period from the Variance Tab, C3 is the year from the variance tab and D2 is the Line that I want to look up.  It is a merged field that combines D2 and D3.

Data is the range that I named for the information on the Data tab.

4 is the column on the data tab that I need the information from.


The return value is 0.  What I am doing wrong in my formula?



1 Reply


Use this, confirmed with Ctrl+Shift+Enter to turn it onto an array formula:


=IFERROR(INDEX(Data!$D$2:$D$100, MATCH(1, (Data!$A$2:$A$100=D2)*(Data!$B$2:$B$100=C3)*(Data!$C$2:$C$100=B3), 0)), "")


Adjust the ranges if needed.