Index , Vlookup Average Formula

S

Sparda142

Board Regular
Joined
Dec 19, 2018
Messages
52
Hello,

I am trouble creating a formula to go into Cells B2:I2, Im trying to have Excel say Find the Text "Scheduled Date" match the date below with a date in Row 1 and provide the information in Column M for Test 1 , Only Information from the Text "Scheduled Date to Subtotal" Belongs to Test 1. I have tried using a few IFS formula with combination of Vlookup but have failed.
Please help me create a formula that will do the job Excel Gods and have mercy on me! :pray:



[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD]Column I [/TD]
[TD]Column J[/TD]
[TD]Column K[/TD]
[TD]Column
L[/TD]
[TD]Column M[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Test G[/TD]
[TD]3/24[/TD]
[TD]3/25[/TD]
[TD]3/25[/TD]
[TD]3/26[/TD]
[TD]3/27[/TD]
[TD]3/28[/TD]
[TD]3/29[/TD]
[TD]3/30[/TD]
[TD]Total Average [/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test 1[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Schedule Date[/TD]
[TD]N/A[/TD]
[TD]Total Ad[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Test 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/26[/TD]
[TD]N/A[/TD]
[TD]93.47%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Test 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/28[/TD]
[TD]N/A[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Test 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/29[/TD]
[TD]N/A[/TD]
[TD]91.07&[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Test 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal:[/TD]
[TD]N/A[/TD]
[TD]94.85%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test 2[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Schedule Date[/TD]
[TD]N/A[/TD]
[TD]Total Ad[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/24
[/TD]
[TD]N/A[/TD]
[TD]21.41%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/25[/TD]
[TD]N/A[/TD]
[TD]67.25%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal:[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test 3[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/29[/TD]
[TD]N/A[/TD]
[TD]41.44%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/30
[/TD]
[TD]N/A[/TD]
[TD]99.99%[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Hi

The easiest way I can think of without using a macro would be to add a helper column, say in N2:N15, that has the appropriate test written in every cell relating to information for that test.

In N2:

=IF(LEFT(K2,4)="Test",K2,N1)

And drag down. Then match on the combination of the date in K and the Test in N. Then, in B2:

=SUMIFS($M$2:$M$15,$K$2:$K$15,B$1,$N$2:$N$15,$A2)

Copy this across to I2 and then drag down to I7. This will only work if you have exactly one match and if your data in column M is numbers.
 
Upvote 0
Yes. Originally Column K had the dates written as "Tuesday, 26 March 2019", I have use a "right" formula with a combination of "Datevalue" and copied over the text with a Date so the dates appear
 
Upvote 0
This may not work due to "Test 1" is a Person and each has a different name length, Attached is the spread sheet i am working on. I;m trying to get the volume in column AB for the Individuals in column B by date.

[FONT=&quot]https://www.mediafire.com/file/o2i9h1bijyj12zb/Adherance_Template.xlsx/file[/FONT]
 
Upvote 0
Without changing the data structure, we'd have to write an equally clunky formula. Much better is to neaten it up like:

Zq7tPrE.png


Then you can pivot as desired.

Here's how to label each row:


Excel 2010
ABCD
1N/AN/A
2Test 1Test 1N/A
3Test 1Schedule DateN/ATotal Ad
4Test 126-MarN/A93.47%
5Test 128-MarN/A100%
6Test 129-MarN/A91.07%
7Test 1Subtotal:N/A94.85%
8Test 2Test 2N/A
9Test 2Schedule DateN/ATotal Ad
10Test 224-MarN/A21.41%
11Test 225-MarN/A67.25%
12Test 2Subtotal:N/A
13Test 3Test 3N/A
14Test 329-MarN/A41.44%
15Test 330-MarN/A99.99%
Sheet6
Cell Formulas
RangeFormula
A2=IF(LEFT(B2,4)="Test",B2,A1)


then delete the extras either by sorting first or with F5-Goto-Special-Text
 
Last edited:
Upvote 0
If the consistent thing that indicates the next test is the "Subtotal:" cell, you could instead check for subtotals like this (in A8):

=IF(B7="Subtotal:",B8,A7)
 
Last edited:
Upvote 0
So I have deleted a bunch of information that wasn't needed and am now here. Would a INDEX & Match with multiple criteria to make the Percent come up in cell b2 if A2 and B1 match?


[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]3/24[/TD]
[TD]3/25[/TD]
[TD]3/26[/TD]
[TD]3/27[/TD]
[TD]empty Column[/TD]
[TD]NAME & Dates[/TD]
[TD]Percent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jacob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jacob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/24[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Scott[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/27[/TD]
[TD]25%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Matt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/25[/TD]
[TD]30%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Howard[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/26[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/27[/TD]
[TD]99%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Scott[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/24[/TD]
[TD]80%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/27[/TD]
[TD]75%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/30[/TD]
[TD]80%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tim[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/29[/TD]
[TD]99%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Matt[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/28[/TD]
[TD]63%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If you must........


Excel 2010
ABCDEFGHIJK
124-Mar25-Mar26-Mar27-Mar28-Mar29-Mar30-Marempty ColumnNAME & DatesPercent
2Jacob100%#N/A#N/A25%#N/A#N/A#N/AJacob
3Jill#N/A30%100%99%#N/A#N/A#N/A24-Mar100%
4Scott80%#N/A#N/A75%#N/A#N/A80%27-Mar25%
5Tim#N/A#N/A#N/A#N/A#N/A99%#N/AJill
6Matt#N/A#N/A#N/A#N/A63%#N/A#N/A25-Mar30%
7Howard#N/A#N/A#N/A#N/A#N/A#N/A#N/A26-Mar100%
827-Mar99%
9Scott
1024-Mar80%
1127-Mar75%
1230-Mar80%
13Tim
1429-Mar99%
15Matt
1628-Mar63%
17Howard
Sheet7
Cell Formulas
RangeFormula
B2=VLOOKUP(B$1,OFFSET(INDEX($J$2:$J$17,MATCH($A2,$J$2:$J$17,0)),,,MATCH($A3,$J$2:$J$17,0)-MATCH($A2,$J$2:$J$17,0),2),2,0)


I might be able to post a slightly shorter version
 
Upvote 0
You must log in or register to reply here.

Similar threads

Lantern
  • Question Question
Excel VBA Loop through Column Range to Mark Section Headers
2
Replies
16
Views
894
Akuini
Akuini
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
422
Alex Blakenburg
A
V
  • Question Question
Help with my excel sheet
Replies
2
Views
290
victoriar
V
T
  • Question Question
How to sum with multiple criteria in 2 different columns? - census data
Replies
1
Views
165
hagia_sofia
hagia_sofia
I
  • Question Question
Chart Data Range - Changes Daily
Replies
9
Views
481
awoohaw
awoohaw

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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