Feb 05 2021 10:29 AM
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?
thanks,
Feb 05 2021 11:18 AM - edited Feb 05 2021 12:01 PM
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.