Offset + Dynamic Index / Match

E

Eyeope

New Member
Joined
Jan 3, 2017
Messages
11
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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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>
 
Sort by date Sort by votes
Eyeope said:
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!
Click to expand...

I have used the file corresponding to the picture in post #13 .
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You must log in or register to reply here.

Similar threads

C
  • Question Question
IF Condition help (I believe)
Replies
3
Views
1K
Joe4
Joe4
B
  • Question Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs
M
  • Question Question
Multiple table array
Replies
5
Views
938
Fluff
Fluff
M
  • Question Question
"Sort by" Formula
2
Replies
11
Views
2K
Marcelo Branco
M
M
  • Question Question
Transposing n rows into columns
Replies
3
Views
1K
molinal
M

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top