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>

 

[James006]

Hi,

Not fully sure to understand your question ...

The Index() function allows you to return data both with the Row and Column values ...

 

[Eyeope]

Thanks James.

So, the first formula returns values from a dynamic range depending on what data validation (month) I've chosen.

For example, if I choose November and with my formula it returns these values:

[TABLE="class: cms_table, width: 87"]
<tbody>[TR]
[TD="class: cms_table_xl63, width: 87, align: left"]65,000 kr[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, align: left"]28,000 kr[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, align: left"]87,000 kr[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, align: left"]0 kr[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, align: left"]49,200 kr[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, align: left"]49,200 kr[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, align: left"]0 kr[/TD]
[/TR]
</tbody>[/TABLE]


I would like values from the other columns returned as well, so the values from the "Date" column (which would be 11/6/18 in case of the first value of 65,000kr in the November column)

Sorry, if I didn't explain it correctly.

Thing is I can't use index match the value 65,000kr, because it is not always unique, there could be duplicate amounts, and if I use that to index match it would return the first value, which may not be the one im looking for.

 

[James006]

Understand your remark about the fact you are dealing with non-unique values ...

Does this mean that you are looking for a formula for the n th match ... ?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-programming-vba-macros/1252118-offset-dynamic-index-match.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Eyeope]

Thanks, yes, I thought it might be easier if I could attach a spreadsheet for users to help.

James - it might be a bit easier if I could attach a spreadsheet?

In K3 and downwards the first formula returns values from a dynamic range depending on what data validation (month) I've chosen ( I would like to only return values that >0 but that's a more nice to have than a need to have).

So, depending on K1, I want it to return the values from that month, and then beside that I would like it to return values from the other columns.

So in a perfect world, if I chose the month November I would get these values from the table (excluding the 0's):

November - Date - Type
65,000 kr - 11/6/18 - Marketing
28,000 kr - 11/1/18 - Marketing
87,000 kr - 10/27/18 - Marketing
49,200 kr - 10/17/18 - Marketing
49,200 kr - 10/12/18 - Marketing

 

[Fluff]

Thanks, yes, I thought it might be easier if I could attach a spreadsheet for users to help.

Whilst it maybe "easier" you still need to let people know.
Also only members of that site can download the file, so it may not help you much.

 

[James006]

Hello,

Looks like a simple equal formula could do the job ... since there is no filtering ...

 

[Eyeope]

In a perfect world I'd like it only to return values >0, so a = wouldn't work..

 

[James006]

Hello again,

With Excel ... everything is possible ...

But ... to make your life easier ... why don't you use AutoFilter ...