Offset + Dynamic Index / Match

[Eyeope]

Hi,

Ive been working on a a dynamic INDEX / MATCH, which I've sovled. How ever the values I'm returning on not always unique, and I want to lookup other values from the same column/row.

<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}x\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</style><![endif]--><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {font-size:10.0pt; font-family:Arial, sans-serif; mso-font-charset:0;}.xl64 {font-size:8.0pt; font-weight:700; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0;}.xl65 {mso-number-format:"Short Date";}--></style>[TABLE="width: 783"]
<!--StartFragment--> <colgroup><col width="87" span="9" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87, align: left"]Date[/TD]
[TD="class: xl64, width: 87, align: left"]Who[/TD]
[TD="class: xl64, width: 87, align: left"]Type[/TD]
[TD="class: xl64, width: 87, align: left"]SUM[/TD]
[TD="class: xl64, width: 87, align: left"]August[/TD]
[TD="class: xl64, width: 87, align: left"]September[/TD]
[TD="class: xl64, width: 87, align: left"]October[/TD]
[TD="class: xl64, width: 87, align: left"]November[/TD]
[TD="class: xl64, width: 87, align: left"]December[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11/6/18[/TD]
[TD="class: xl63, align: left"]Chris[/TD]
[TD="class: xl63, align: left"]Marketing[/TD]
[TD="class: xl63, align: left"]104,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]39,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]65,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11/1/18[/TD]
[TD="class: xl63, align: left"]Chris[/TD]
[TD="class: xl63, align: left"]Marketing[/TD]
[TD="class: xl63, align: left"]70,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]42,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]28,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10/27/18[/TD]
[TD="class: xl63, align: left"]Chris[/TD]
[TD="class: xl63, align: left"]Marketing[/TD]
[TD="class: xl63, align: left"]116,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]29,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]87,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10/22/18[/TD]
[TD="class: xl63, align: left"]Chris[/TD]
[TD="class: xl63, align: left"]Marketing[/TD]
[TD="class: xl63, align: left"]112,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]48,000 kr[/TD]
[TD="class: xl63, align: left"]64,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10/17/18[/TD]
[TD="class: xl63, align: left"]Chris[/TD]
[TD="class: xl63, align: left"]Marketing[/TD]
[TD="class: xl63, align: left"]123,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]36,900 kr[/TD]
[TD="class: xl63, align: left"]36,900 kr[/TD]
[TD="class: xl63, align: left"]49,200 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10/12/18[/TD]
[TD="class: xl63, align: left"]Chris[/TD]
[TD="class: xl63, align: left"]Marketing[/TD]
[TD="class: xl63, align: left"]123,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]36,900 kr[/TD]
[TD="class: xl63, align: left"]36,900 kr[/TD]
[TD="class: xl63, align: left"]49,200 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10/7/18[/TD]
[TD="class: xl63, align: left"]Chris[/TD]
[TD="class: xl63, align: left"]Marketing[/TD]
[TD="class: xl63, align: left"]123,000 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]36,900 kr[/TD]
[TD="class: xl63, align: left"]36,900 kr[/TD]
[TD="class: xl63, align: left"]0 kr[/TD]
[TD="class: xl63, align: left"]49,200 kr[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


I have a data validation with the months which I've solved like this: =INDEX(Sheet1!$E$3:$I$1000,0,MATCH($K$1,Sheet1!$E$2:$I$2,0)) - so if I chose November from the dropdown list, I return all the values from the November column: <!--[if !mso]><style>v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}x\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</style><![endif]--><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {color:black; font-size:10.0pt; font-family:Inconsolata; mso-generic-font-family:auto; mso-font-charset:0; background:yellow; mso-pattern:black none;}--></style>[TABLE="width: 87"]
<!--StartFragment--> <colgroup><col width="87" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 87, align: left"]65,000 kr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: left"]28,000 kr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: left"]87,000 kr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: left"]49,200 kr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: left"]49,200 kr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: left"]0 kr[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

Now, I would like to return the values from the other columns as well, for example for the first returned value in November I get "65,000kr", I would like to return the values from the Date, Who, Type columns as well.

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {color:black; font-size:10.0pt; font-family:Inconsolata; mso-generic-font-family:auto; mso-font-charset:0; background:yellow; mso-pattern:black none;}--></style>

 

[Eyeope]

Thanks James,

Because the people who will be using this won't be as adamant as I am with adding 0's, even though I can filter on blanks.

I also don't want the auto filter to jump from row 3 --> 7 depending on the 0's. I'd like to have it 'clean'

 

[James006]

Hello again,

In cell K2 ... just under your Validation month ...

=SUBSTITUTE(ADDRESS(1,MATCH($K$1,Underlag!$A$2:$I$2,0),4),1,"")

in cell K3 ...the following array formula :

=IFERROR(INDEX(INDIRECT($K$2&":"&$K$2),SMALL(IF((INDIRECT($K$2&"3:"&$K$2&"69"))<>"0 kr",ROW(INDIRECT($K$2&"3:"&$K$2&"69"))),ROWS($A$3:A3))),"")

Formula can then be copied down to rows 4 to 69 ...

in cell L3 ... for the Date ... the following array formula :

=IFERROR(INDEX(A:A,SMALL(IF((INDIRECT($K$2&"3:"&$K$2&"69"))<>"0 kr",ROW(INDIRECT($K$2&"3:"&$K$2&"69"))),ROWS($A$3:A3))),"")

Formula can be copied to Columns M and N to get Who and Type ... and also down to rows 4 to 69 ...

Hope this will help

 

[Eyeope]

Thank you I'm getting blanks though? :/

Also, the real sheet I'll be working with will keep adding data, and in the formula the number "69" would have to be manually updated?

Thank you so much again!

 

[James006]

Hello,

A couple of remarks :

1. When using an Array Formula ... instead of the standard Enter key ... you need to use simultaneously the three keys : Control Shift Enter

2. As an initial step ... you do need to replace the number 69 by your actual last row number ...

Hope this clarifies

 

[Eyeope]

Thank you so much for your help! Good thing, it works great in Excel, but trying to transfer over the formula to Google Sheets (data is formatted a bit differently) and it's not working :/

https://docs.google.com/spreadsheet...WdNWv9U9Q1OIJ60ww5oEd3D0o/edit#gid=1036533333

I've copied over to a new google sheet!

Many thanks for all your help!!

 

[Aladin Akyurek]

In O3 implement the following array formula and copy down:

=ARRAYFORMULA(IFERROR(SMALL(IF(LEFT(INDEX($E$3:$I$69,0,MATCH($K$1,$E$2:$I$2,0)))<>"0",ROW($E$3:$I$69)-ROW($E$3)+1),ROWS($1:1)),""))

In K3 enter and copy down:

=IF($O3="","",INDEX($E$3:$I$69,$O3,MATCH($K$1,$E$2:$I$2,0)))

In L3 enter, copy across to N3, and down:

=IF($O3="","",INDEX(A$3:A$69,$O3))

 

[Eyeope]

In O3 implement the following array formula and copy down:

=ARRAYFORMULA(IFERROR(SMALL(IF(LEFT(INDEX($E$3:$I$69,0,MATCH($K$1,$E$2:$I$2,0)))<>"0",ROW($E$3:$I$69)-ROW($E$3)+1),ROWS($1:1)),""))

In K3 enter and copy down:

=IF($O3="","",INDEX($E$3:$I$69,$O3,MATCH($K$1,$E$2:$I$2,0)))

In L3 enter, copy across to N3, and down:

=IF($O3="","",INDEX(A$3:A$69,$O3))

Not in the google doc this time?

 

[James006]

Thank you so much for your help! Good thing, it works great in Excel, but trying to transfer over the formula to Google Sheets (data is formatted a bit differently) and it's not working :/

https://docs.google.com/spreadsheet...WdNWv9U9Q1OIJ60ww5oEd3D0o/edit#gid=1036533333

I've copied over to a new google sheet!

Many thanks for all your help!!

Glad to hear it did help you out ...

 

[Aladin Akyurek]

Not in the google doc this time?

This should work in Google Sheets.

 

[Eyeope]

This should work in Google Sheets.

Sorry, to be a nuisance. Just can't get it to work, I have column headers in the "monthly" tab, where i want the values from the A1 to appear (if i chose september, i want it to collect on the values >0 from underlag!M:M (september column), and then in C2:I2 (and more to the right), I want to find the right value / lookup.

Thank you for spending time and helping me!