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

ccivitella01_0-1612549118349.png

Data Tab

ccivitella01_1-1612549255853.png

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?

 

thanks,

1 Reply

@ccivitella01 

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.