pacerfan07
New Member
- Joined
- Jun 2, 2017
- Messages
- 20
Hi,
I have the following worksheet titled "Lag Data"
[/URL][/IMG]
In column D I want to look up data from the following spreadsheet titled "Lag Data 0518 - 0419"
[/URL][/IMG]
Cell D4 in the first spreadsheet should return a value of $1,654,010.87 from the second spreadsheet. That's because cells B4 and C4 in the first spreadsheet agree to cells C3 and B4 in the second spreadsheet. The formula should also check to make sure the SubGroupNames in column A match (that's where the data from columns O and P in the first spreadsheet come into play).
I'm currently using the following formula but it will not work (please note I am remembering to hit ctrl+shift+enter to run the array).
=INDEX(OFFSET('Lag Data 0518 - 0419'!$B$3,1+VLOOKUP('Lag Data'!A4,'Lag Data'!$O$4:$P$9,2,FALSE)*12,1,12,12),MATCH('Lag Data'!C4,'Lag Data 0518 - 0419'!$B$4:$B$15,0),MATCH(TEXT('Lag Data'!B4,"@"),'Lag Data 0518 - 0419'!$C$3:$N$3,0))
I have the following worksheet titled "Lag Data"
In column D I want to look up data from the following spreadsheet titled "Lag Data 0518 - 0419"
Cell D4 in the first spreadsheet should return a value of $1,654,010.87 from the second spreadsheet. That's because cells B4 and C4 in the first spreadsheet agree to cells C3 and B4 in the second spreadsheet. The formula should also check to make sure the SubGroupNames in column A match (that's where the data from columns O and P in the first spreadsheet come into play).
I'm currently using the following formula but it will not work (please note I am remembering to hit ctrl+shift+enter to run the array).
=INDEX(OFFSET('Lag Data 0518 - 0419'!$B$3,1+VLOOKUP('Lag Data'!A4,'Lag Data'!$O$4:$P$9,2,FALSE)*12,1,12,12),MATCH('Lag Data'!C4,'Lag Data 0518 - 0419'!$B$4:$B$15,0),MATCH(TEXT('Lag Data'!B4,"@"),'Lag Data 0518 - 0419'!$C$3:$N$3,0))
Last edited: