Index array

[pacerfan07]

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))

 

[Fluff]

Re: Help with index array

How about
=INDEX('Lag Data 0518 - 0419'!$C$4:$N$15,MATCH(B4&"|"&A4,'Lag Data 0518 - 0419'!$B$4:$B$14&"|"&'Lag Data 0518 - 0419'!$A$4:$A$14,0),MATCH(C4,'Lag Data 0518 - 0419'!$C$3:$N$3,0))

Confirm with CSE

 

[pacerfan07]

Re: Help with index array

Thank you, it does work for cell D4. However when I drag the formula down it's coming up with zero for the next few cells when it shouldn't.

Additionally, column A eventually changes to say "Active Secure" (as seen in the data in columns O and P on the first spreadsheet). When I drag the formula down and reach the point where "Active Secure" is in column A, it starts returning N/A as a result.

 

[Fluff]

Re: Help with index array

In that case you will need to supply some data, as it's impossible to debug with nothing but an image.
There are some Add-ins here https://www.mrexcel.com/forum/about-board/508133-attachments.html that will enable you to post a small sample of data to the board.

Also please explain where cols O & P come in. As you said the the subgroups in Col A must match.

 

[pacerfan07]

Re: Help with index array

Thank you, I was able to debug it. The items in row 3 on the second spreadsheet needed to be converted to TEXT. That made my original formula work.

 

[Fluff]

Re: Help with index array

Glad you sorted it & thanks for the feedback