I have three spreadsheets. I am looking for a formula that will Lookup the CODE in Sheet 2 that matches C1 & B2 in Sheet 3 and return the value in Sheet 1 for that CODE that falls in the Date Range in Sheet 1 B1: H2. I have attached a sample of what I am working with and hoping someone can assist? Thank you in advance to taking the time to look!
try this, copied down and across...
=IFERROR(INDEX(Sheet1!$A$2:$H$6,MATCH(INDEX(Sheet2!$B$1:$G$5,MATCH(Sheet3!$B2,Sheet2!$B$1:$B$5,0),MATCH(Sheet3!C$1,Sheet2!$B$1:$G$1,0)),Sheet1!$A$2:$A$6,0),MATCH(Sheet3!C$1,Sheet1!$A$2:$H$2,0)),B2)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I tried it and it didn't return an error which is WAY further than I was able to get, but when the formula is copied over to where the date changes to the next segment of the DATE RANGE in Sheet 1 (in this example changing to 1/1/2016 or later) it value that is returned is still matching to the 1/1/2015 - 12/31/2015 date range.
=IFERROR(INDEX(Sheet1!$C$3:$H$6,MATCH(INDEX(Sheet2!$C$2:$M$5,MATCH($B2,Sheet2!$B$2:$B$5,0),MATCH(C$1,Sheet2!$C$1:$M$1,0)),Sheet1!$A$3:$A$6,0),MATCH(YEAR(C$1)&MONTH(Sheet1!$C$2:$H$2),YEAR(Sheet1!$C$2:$H$2)&MONTH(Sheet1!$C$2:$H$2),0)),"")
Confirm COntrol+Shift+Enter
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
When I copy it over it returns blank results beginning at 6/1/2015. If I change D2 to 4/1/2016 it returns a blank result as well. This formula is so far beyond my Excel skills I wouldn't even know where to begin trying to find where or why it stops working on the later dates...any ideas?
LookupTest-1.xlsx
Please see attached.
You need to extend all ranges and add data or copy this formula to your main sheet and then extend all ranges
OMG! Can I just say that is AMAZING! Every time I come to this forum I am reminded of how little I know about the capabilities of Excel. Thank you so much! I was muddling through trying to solve that formula for days! I think it will take me a little bit of studying to understand all of it....Thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks