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
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' :)
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hello again,

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

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

in cell K3 ...the following array formula :

Code: 
=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 :

Code: 
=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

:smile:
 
Upvote 0
Thank you :) I'm getting blanks though? :/

vslyAX0.png


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!
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
Aladin Akyurek said:
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))
Click to expand...

Not in the google doc this time?
 
Upvote 0
Upvote 0
Aladin Akyurek said:
This should work in Google Sheets.
Click to expand...

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!
 
Upvote 0
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
935
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,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

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