
Combine Index+match Functions With Indirect Formula


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Combine Index+match Functions With Indirect Formula  Excel

View Answers


Hi,
I need some heads up on how to combine some formulas. Here is my formula:
=INDEX(A5:T100,ROW(5:5),MATCH(L4,A4:T4,0))
Arrays are in 3 separate sheets (similar layout). Cell L3 would represent sheet name. So, whatever sheet name is in L3, formula is working with that sheet.
Thanks in advance!
Similar Excel Video Tutorials
INDIRECT function, Names and LOOKUP
 THE most amazing Name Trick: See how to use Data Validation, the INDIRECT function, the space operator and Names to look things up! Two 2 W ...
Similar Topics
Hello, first post, hope someone can help...
My workbook has a sheet for each month (P1, P2, P3, etc...) and a summary sheet that looks at data in the most recent month sheet.
Each time I add a new month, I want a quick way to tell the summary sheet to refer to the new month sheet. I've got this to work up to a point by naming the data range in the month sheets, entering the range name required in a cell on my summary sheet and using INDIRECT to refer to that range. Then I only need change the cell with the range name on the summary sheet to get the formulas to look at the new sheet.
My problem is that the the data I want to look at on the month sheets can change position / size, so it would be useful to use INDEX, MATCH formulas to tell my summary where to look. I can't get INDEX, MATCH to work in conjunction with INDIRECT.
Example:
=INDEX(INDIRECT(C1),MATCH(A4,P11_2007!C17:C29,0),MATCH(C2,P11_2007!A17:N17,0))
This works because I have told it exactly where to look for MATCH  C17:C29; A17:N17, but if the location of that lookup changes, the formula will fail.
I tried:
=INDEX(INDIRECT(C1),MATCH(A4,INDIRECT(C1),0),MATCH(C2,INDIRECT(C1),0))
but it returns #n/a, the column and row lookups for MATCH won't work with INDIRECT.
H E L P ! ! !
I have a worksheet which I use as a form. On this form, there is a cell where I enter a textual reference number. In the cell below I have written an INDEX MATCH formula that searches a different sheet for the same reference number and then returns informaiton pertaining to that reference number.
This worked well for searching on the 1 x different sheet however I need the formula to be able to search from 3 other different sheets as well (the other sheets all have the same layout) to match the 1 unique textual reference number.
This means that I need to work something out where I can broaden my arrays in both the INDEX and the MATCH formula to cover a number of sheets.
This is the formula that I used:
=INDEX(KINC!$A$6:$Z$207,MATCH('Notification Form'!$D5,KINC!$B$6:$B$209,0),3)
Indexing sheet 1, to match ref on sheet 2, returning corresponding info for the matched ref, of an exact match, in column 3.
I hope this makes sense! & I hope someone out there can help because it is bending my brain!
Thanks
Hi guys.. newbie here...
Sorry why cant I post attachments?
I need help with a sheet/ formula.
Basically I have a cell which needs to pick up prices from 2 ranges using a matching criteria. The condition is that a "c" will show index match of one range and no "c" will show index match of another range name.
=IF(C5="c",(INDEX(CashPrices,MATCH(A7,codes,)),(INDEX(Invoiceprices,MATCH(A7,codes,)))))
The problem is when I am entering the 2 formulas seperately in seperate cells they work out but when I combine them and this is what i need a get a "value" error??
vhy o vhy!!??
I looove excel!
Hello,
I have a range of data that I would like to conditionally format all cells that have an error to have white font. Unfortunately, I cannot use a function like this:
if(iserror(function,"",function))
because the formulas in this range are already too long to be able to duplicate for the 'value if false' argument. I have also tried using the conditional formatting tool by setting "Formula Is" equal to:
=ISERROR($A$1)
but this only whites out errors in cell A1 or column A (depending on what I selected.
I have pasted the formula below that is giving me the error if anyone has any ideas on how to modify the formula as well. It is basically an index match formula that is looking at several different data points on another worksheet and averaging them. Thanks in advance.
=AVERAGE(INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$2,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH( $C18,SRD!$A$4:$A$351,0),MATCH(M$3,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$4,SRD! $D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$5,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351, MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$6,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$ 7,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$13,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$ DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$8,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),M ATCH(M$9,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$10,SRD!$D$2:$DG$2,0)),INDEX(SRD !$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$11,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$ 351,0),MATCH(M$12,SRD!$D$2:$DG$2,0)))
Hi, I am new here and thanks for making this resource available.
I have a problem with a Conditional Format I am setting up. I almost think it is a bug in Excel.
I have two formulas that both use INDIRECT().
I have put both formulas into cells on the worksheet and both ring TRUE or FALSE depending on the condition. They work properly.
If I put either of the two formulas in the CF by itself, the CF also works fine for that formula individually.
However if I include both formulas into an AND() function, then the CF does not toggle.
However, if I reference the two cells the INDIRECT() statements are in, the CF works fine.
Code:
AND(M1,M2)
M1 = INDIRECT("I"&ROW(INDEX(B$28:B$60,MATCH($K28,B$28:B$60,0))))>0
M2 = INDIRECT("F"&ROW(INDEX(B$28:B$60,MATCH($K28,B$28:B$60,0))))0,INDIRECT("F"&ROW(INDEX(B$28:B$60,MATCH($K28,B$28:B$60,0))))0,INDIRECT("F"&ROW(INDEX(B$28:B$60,MATCH($K28,B$28:B$60,0))))0,INDIRECT("F"&ROW(INDEX(B$28:B$60,MATCH($K28,B$28:B$60,0))))
Hi,
Whenever i input the following formula, a messagebox pops up and tells me, "Formula is too long". Can someone help me shorten this formula? In its current form it's 1535 character long and needs to be less than 1035.
VB:
=If(record_index3=1,(If(record_index=1,INDEX($H$551:$BC$566,MATCH($C$909,$G$551:$G$566,False),4),If(record_index=2,INDEX($H$568:$BC$583,MATCH($C$909,$G$568:$G$583,False),4),If(record_index=3,INDEX($H$585:$BC$600,MATCH($C$909,$G$585:$G$600,False),4),INDEX($H$759:$BC$774,MATCH($C$909,$G$759:$G$774,False),4)))))),If(record_index3=2,(If(record_index=1,INDEX($H$603:$BC$618,MATCH($C$909,$G$603:$G$618,False),4),If(record_index=2,INDEX($H$620:$BC$635,MATCH($C$909,$G$620:$G$635,False),4),If(record_index=3,INDEX($H$637:$BC$652,MATCH($C$909,$G$637:$G$652,False),4),INDEX($H$776:$BC$791,MATCH($C$909,$G$776:$G$791,False),4)))))),If(record_index3=3,(If(record_index=1,INDEX($H$655:$BC$670,MATCH($C$909,$G$655:$G$670,False),4),If(record_index=2,INDEX($H$672:$BC$687,MATCH($C$909,$G$672:$G$687,False),4),If(record_index=3,INDEX($H$689:$BC$704,MATCH($C$909,$G$689:$G$704,False),4),INDEX($H$793:$BC$808,MATCH($C$909,$G$793:$G$808,False),4)))))),If(record_index3=4,(If(record_index=1,INDEX($H$707:$BC$722,MATCH($C$909,$G$707:$G$722,False),4),If(record_index=2,INDEX($H$724:$BC$739,MATCH($C$909,$G$724:$G$739,False),4),If(record_index=3,INDEX($H$741:$BC$756,MATCH($C$909,$G$741:$G$756,False),4),INDEX($H$810:$BC$825,MATCH($C$909,$G$810:$G$825,False),4)))))),If(record_index=1,INDEX($H$827:$BC$842,MATCH($C$909,$G$827:$G$842,False),4),If(record_index=2,INDEX($H$844:$BC$859,MATCH($C$909,$G$844:$G$859,False),4),If(record_index=3,INDEX($H$861:$BC$876,MATCH($C$909,$G$861:$G$876,False),4),INDEX($H$878:$BC$893,MATCH($C$909,$G$878:$G$893,False),4))))))
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I was able to Name $C$909 to NM and change FALSE to 0 and shorten the names of record_index and record_index3, but that only got me to 1185. It's the MATCH and INDEX functions taking up space I guess.
Thanks in advance for any help.
Excel Lurker
Hi all,
I am trying to insert a formula to a cell with the line below.
ActiveCell.FormulaR1C1 = "=IF(OR($E$6=Balance!$H$26,$E$6=Balance!$H$27,$E$6=Balance!$H$28),INDEX(INDIRECT("'XA"&$E$6&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$6&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$6&"'!$E$14:$GH$14"),0)),INDEX(INDIRECT("'XA"&$E$6&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$6&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$6&"'!$E$14:$GH$14"),0))+INDEX(INDIRECT("'XA"&$E$7&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$7&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$7&"'!$E$14:$GH$14"),0)))"
The issue I am having is that VBA code comments out the part of the formula that comes after ', see below.
(INDIRECT(" 'XA
Thanks in advance.
Need some help here!
Week 3 if Data has been entered Week 4 returns an error N/A.
If no data is entered example H17 then the formula fine
What I'm looking for in week 4 is not to return an N/A error if no data has been entered.
Any help much appreciated.
Killer
*
B
C
D
E
F
G
H
I
J
K
14
*
Start
Week 1
Week 2
Week 3
Week 4
15
Rob W.
$20
PIT
v
IND
X
OAK
X
#N/A
#
16
Alene
$20
NYJ
X
NE
v
GB
X
#N/A
#
17
Steve W.
$20
SF
P
IND
X
*
*
*
*
Spreadsheet Formulas
Cell
Formula
D15
=IF(INDEX('W1'!$AQ$10:$AQ$49, MATCH($B15,'W1'!$B$10:$B$49,0))="", "",INDEX('W1'!$AQ$10:$AQ$49,MATCH($B15,'W1'!$B$10:$B$49,0)))
E15
{=IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$54:AL$54,0)),"v",IF(INDEX('W1'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(D15,'W1'!C$51:AL$51&'W1'!C$52:AL$52)),0))="Push","P","X")))}
F15
=IF(INDEX('W2'!$AQ$10:$AQ$49, MATCH($B15,'W2'!$B$10:$B$49,0))="", "",INDEX('W2'!$AQ$10:$AQ$49,MATCH($B15,'W2'!$B$10:$B$49,0)))
G15
{=IF(F15="","",IF(ISNUMBER(MATCH(F15,'W2'!C$54:AL$54,0)),"v",IF(INDEX('W2'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(F15,'W2'!C$51:AL$51&'W2'!C$52:AL$52)),0))="Push","P","X")))}
H15
=IF(INDEX('W3'!$AQ$10:$AQ$49, MATCH($B15,'W3'!$B$10:$B$49,0))="", "",INDEX('W3'!$AQ$10:$AQ$49,MATCH($B15,'W3'!$B$10:$B$49,0)))
I15
{=IF(H15="","",IF(ISNUMBER(MATCH(H15,'W3'!C$54:AL$54,0)),"v",IF(INDEX('W3'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(H15,'W3'!C$51:AL$51&'W3'!C$52:AL$52)),0))="Push","P","X")))}
J15
=IF(INDEX('W4'!$AQ$10:$AQ$49, MATCH($B15,'W4'!$B$10:$B$49,0))="", "",INDEX('W4'!$AQ$10:$AQ$49,MATCH($B15,'W4'!$B$10:$B$49,0)))
K15
{=IF(J15="","",IF(ISNUMBER(MATCH(J15,'W4'!C$54:AL$54,0)),"v",IF(INDEX('W4'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(J15,'W4'!C$51:AL$51&'W4'!C$52:AL$52)),0))="Push","P","X")))}
D16
=IF(INDEX('W1'!$AQ$10:$AQ$49, MATCH($B16,'W1'!$B$10:$B$49,0))="", "",INDEX('W1'!$AQ$10:$AQ$49,MATCH($B16,'W1'!$B$10:$B$49,0)))
E16
{=IF(D16="","",IF(ISNUMBER(MATCH(D16,'W1'!C$54:AL$54,0)),"v",IF(INDEX('W1'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(D16,'W1'!C$51:AL$51&'W1'!C$52:AL$52)),0))="Push","P","X")))}
F16
=IF(INDEX('W2'!$AQ$10:$AQ$49, MATCH($B16,'W2'!$B$10:$B$49,0))="", "",INDEX('W2'!$AQ$10:$AQ$49,MATCH($B16,'W2'!$B$10:$B$49,0)))
G16
{=IF(F16="","",IF(ISNUMBER(MATCH(F16,'W2'!C$54:AL$54,0)),"v",IF(INDEX('W2'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(F16,'W2'!C$51:AL$51&'W2'!C$52:AL$52)),0))="Push","P","X")))}
H16
=IF(INDEX('W3'!$AQ$10:$AQ$49, MATCH($B16,'W3'!$B$10:$B$49,0))="", "",INDEX('W3'!$AQ$10:$AQ$49,MATCH($B16,'W3'!$B$10:$B$49,0)))
I16
{=IF(H16="","",IF(ISNUMBER(MATCH(H16,'W3'!C$54:AL$54,0)),"v",IF(INDEX('W3'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(H16,'W3'!C$51:AL$51&'W3'!C$52:AL$52)),0))="Push","P","X")))}
J16
=IF(INDEX('W4'!$AQ$10:$AQ$49, MATCH($B16,'W4'!$B$10:$B$49,0))="", "",INDEX('W4'!$AQ$10:$AQ$49,MATCH($B16,'W4'!$B$10:$B$49,0)))
K16
{=IF(J16="","",IF(ISNUMBER(MATCH(J16,'W4'!C$54:AL$54,0)),"v",IF(INDEX('W4'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(J16,'W4'!C$51:AL$51&'W4'!C$52:AL$52)),0))="Push","P","X")))}
D17
=IF(INDEX('W1'!$AQ$10:$AQ$49, MATCH($B17,'W1'!$B$10:$B$49,0))="", "",INDEX('W1'!$AQ$10:$AQ$49,MATCH($B17,'W1'!$B$10:$B$49,0)))
E17
{=IF(D17="","",IF(ISNUMBER(MATCH(D17,'W1'!C$54:AL$54,0)),"v",IF(INDEX('W1'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(D17,'W1'!C$51:AL$51&'W1'!C$52:AL$52)),0))="Push","P","X")))}
F17
=IF(INDEX('W2'!$AQ$10:$AQ$49, MATCH($B17,'W2'!$B$10:$B$49,0))="", "",INDEX('W2'!$AQ$10:$AQ$49,MATCH($B17,'W2'!$B$10:$B$49,0)))
G17
{=IF(F17="","",IF(ISNUMBER(MATCH(F17,'W2'!C$54:AL$54,0)),"v",IF(INDEX('W2'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(F17,'W2'!C$51:AL$51&'W2'!C$52:AL$52)),0))="Push","P","X")))}
H17
=IF(INDEX('W3'!$AQ$10:$AQ$49, MATCH($B17,'W3'!$B$10:$B$49,0))="", "",INDEX('W3'!$AQ$10:$AQ$49,MATCH($B17,'W3'!$B$10:$B$49,0)))
I17
{=IF(H17="","",IF(ISNUMBER(MATCH(H17,'W3'!C$54:AL$54,0)),"v",IF(INDEX('W3'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(H17,'W3'!C$51:AL$51&'W3'!C$52:AL$52)),0))="Push","P","X")))}
J17
=IF(INDEX('W4'!$AQ$10:$AQ$49, MATCH($B17,'W4'!$B$10:$B$49,0))="", "",INDEX('W4'!$AQ$10:$AQ$49,MATCH($B17,'W4'!$B$10:$B$49,0)))
K17
{=IF(J17="","",IF(ISNUMBER(MATCH(J17,'W4'!C$54:AL$54,0)),"v",IF(INDEX('W4'!C$54:AL$54,MATCH(1,ISNUMBER(SEARCH(J17,'W4'!C$51:AL$51&'W4'!C$52:AL$52)),0))="Push","P","X")))}
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> Excel Jeanie HTML 4
Hi,
Looking to Improve the formula in the tons/mo column D63 through D67, that is while the INDEX/MATCH approach is working, I believe to be a bit clumsy and not as flexible. Is there an alternative worksheet function combination that will allow the lookup and summing portion on noncontiguous ranges instead of summing multiple INDEX/MATCH's? Perhaps INDIRECT, ROW, OFFSET etc.
To allow for display, I hid rows that have other data in them, thus the rows must be preserved. Also, in the January 2008 case there are no values in the first range, but there will be in other months/sheets so I need to include this range as well.
January 2008
*
B
C
D
E
F
G
H
I
J
K
L
5
January 2008
*
*
*
*
*
*
*
*
*
*
6
*
21
Pollutant
Emissions @ Power Setting (lb)
*
*
*
Total (tons)
Emissions @ Power Setting (lb)
*
*
*
Total (tons)
22
CO
0
0
0
0
0.00
0
0
0
0
0.00
23
VOC
0
0
0
0
0.00
0
0
0
0
0.00
24
NOX
0
0
0
0
0.00
0
0
0
0
0.00
25
SO2
0
0
0
0
0.00
0
0
0
0
0.00
26
PM
0
0
0
0
0.00
0
0
0
0
0.00
36
Pollutant
Emissions @ Power Setting (lb)
*
*
*
Total (tons)
Emissions @ Power Setting (lb)
*
*
*
Total (tons)
37
CO
76
10
9
4
0.05
197
4
11
7
0.11
38
VOC
6
1
1
1
0.00
16
0
1
0
0.01
39
NOX
18
68
453
342
0.44
43
32
733
805
0.81
40
SO2
1
2
7
4
0.01
4
1
11
9
0.01
41
PM
6
9
30
17
0.03
16
5
46
38
0.05
42
*
*
*
*
*
*
*
*
*
*
*
51
Pollutant
Emissions @ Power Setting (lb)
*
*
*
Total (tons)
Emissions @ Power Setting (lb)
*
*
*
Total (tons)
52
CO
196
9
23
20
0.124
546
7
24
14
0.296
53
VOC
10
0
1
1
0.01
45
0
1
0
0.02
54
NOX
31
27
454
466
0.49
120
51
1569
1557
1.65
55
SO2
3
1
10
9
0.012
11
2
24
18
0.027
56
PM
12
5
42
37
0.05
45
8
98
74
0.11
57
*
*
*
*
*
*
*
*
*
*
*
60
*
*
*
*
*
*
*
*
*
*
*
61
*
Pollutant Summary
*
*
*
*
*
*
62
*
Pollutant *
tons/mo
ave lbs/day
*
*
*
*
*
*
*
63
*
CO
0.579
37.37
*
*
*
*
*
*
*
64
*
VOC
0.043
2.76
*
*
*
*
*
*
*
65
*
NOx
3.384
218.34
*
*
*
*
*
*
*
66
*
SO2
0.059
3.81
*
*
*
*
*
*
*
67
*
PM
0.245
15.82
*
*
*
*
*
*
*
68
*
*
*
*
*
*
*
*
*
*
*
69
*
Number of Days This Month:
*
*
31
*
*
*
*
*
*
Spreadsheet Formulas
Cell
Formula
D63
=INDEX($L$52:$L$56,MATCH(C63,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C63,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C63,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C63,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C63,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C63,$B$22:$B$26,0))
E63
=D63*2000/$F$69
D64
=INDEX($L$52:$L$56,MATCH(C64,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C64,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C64,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C64,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C64,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C64,$B$22:$B$26,0))
E64
=D64*2000/$F$69
D65
=INDEX($L$52:$L$56,MATCH(C65,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C65,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C65,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C65,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C65,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C65,$B$22:$B$26,0))
E65
=D65*2000/$F$69
D66
=INDEX($L$52:$L$56,MATCH(C66,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C66,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C66,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C66,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C66,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C66,$B$22:$B$26,0))
E66
=D66*2000/$F$69
D67
=INDEX($L$52:$L$56,MATCH(C67,$B$52:$B$56,0))+INDEX($G$52:$G$56,MATCH(C67,$B$52:$B$56,0))+INDEX($G$37:$G$41,MATCH(C67,$B$37:$B$41,0))+INDEX($L$37:$L$41,MATCH(C67,$B$37:$B$41,0))+INDEX($G$22:$G$26,MATCH(C67,$B$22:$B$26,0))+INDEX($L$22:$L$26,MATCH(C67,$B$22:$B$26,0))
E67
=D67*2000/$F$69
F69
=DAY(DATE(YEAR($B$5),MONTH($B$5)+1,1)1)
Excel tables to the web >> Excel Jeanie HTML 4
I've been using a long If formula in order to eliminate #n/a values:
=IF(ISNA(INDEX(INDIRECT(D$1&"!$A:$AZ"),MATCH($A3,INDIRECT(D$1&"!$A:$A"),0),MATCH(D$2,INDIRECT(D$1&"!$1:$1"),0))),"",INDEX(INDIRECT(D$1&"!$A:$AZ"),MATCH($A3,INDIRECT(D$1&"!$A:$A"),0),MATCH(D$2,INDIRECT(D$1&"!$1:$1"),0)))
Is there a shorter way to elimate the if statement and do the following:
For each cell in selection
.value=INDEX(INDIRECT(D$1&"!$A:$AZ"),MATCH($A3,INDIRECT(D$1&"!$A:$A"),0),MATCH(D$2,INDIRECT(D$1&"!$1:$1"),0))
Eliminate all #n/a
Next cell
Hi All,
Below is the data which I have in 12 worksheets jan to dec I need to calculate the total amount of each staff in sheet total with staff number.
Thanks
AB
Jan
K
L
M
7
STAFF NO
NAME
AMT
8
5223
Annaleyva Abellana
2381
9
5734
Sojan Mathew
1487
10
6050
Min Kyung Kim
1150
11
4486
Malek El Haddad
858
12
5227
Nichollette Paglinawan
852
13
6273
Joanne D'Souza
778
14
2459
Najoua Choukry
756
15
2225
Mona Attia
696
16
7975
Deidre Rodrigues
669
17
6560
Emran Mukaddam
624
18
3780
Pritam Mahajan
592
19
5191
Fahd Shanawaz
562
20
20278
Abd Rahim Bato A Rahman
559
21
5189
Mohamad Doughan
558
22
5225
Elsie Sicat
529
Spreadsheet Formulas
Cell
Formula
K8
=INDEX(B$8:B108,MATCH (ROWS ($C$8:$C8) ,$J$8:$J$108,0) )
L8
=INDEX(C$8:C108,MATCH (ROWS ($C$8:$C8) ,$J$8:$J$108,0) )
M8
=INDEX(F$8:F$108,MATCH (ROWS ($C8:$C$8) ,$J$8:$J$108,0) )
K9
=INDEX(B$8:B109,MATCH (ROWS ($C$8:$C9) ,$J$8:$J$108,0) )
L9
=INDEX(C$8:C109,MATCH (ROWS ($C$8:$C9) ,$J$8:$J$108,0) )
M9
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C9) ,$J$8:$J$108,0) )
K10
=INDEX(B$8:B110,MATCH (ROWS ($C$8:$C10) ,$J$8:$J$108,0) )
L10
=INDEX(C$8:C110,MATCH (ROWS ($C$8:$C10) ,$J$8:$J$108,0) )
M10
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C10) ,$J$8:$J$108,0) )
K11
=INDEX(B$8:B111,MATCH (ROWS ($C$8:$C11) ,$J$8:$J$108,0) )
L11
=INDEX(C$8:C111,MATCH (ROWS ($C$8:$C11) ,$J$8:$J$108,0) )
M11
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C11) ,$J$8:$J$108,0) )
K12
=INDEX(B$8:B112,MATCH (ROWS ($C$8:$C12) ,$J$8:$J$108,0) )
L12
=INDEX(C$8:C112,MATCH (ROWS ($C$8:$C12) ,$J$8:$J$108,0) )
M12
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C12) ,$J$8:$J$108,0) )
K13
=INDEX(B$8:B113,MATCH (ROWS ($C$8:$C13) ,$J$8:$J$108,0) )
L13
=INDEX(C$8:C113,MATCH (ROWS ($C$8:$C13) ,$J$8:$J$108,0) )
M13
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C13) ,$J$8:$J$108,0) )
K14
=INDEX(B$8:B114,MATCH (ROWS ($C$8:$C14) ,$J$8:$J$108,0) )
L14
=INDEX(C$8:C114,MATCH (ROWS ($C$8:$C14) ,$J$8:$J$108,0) )
M14
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C14) ,$J$8:$J$108,0) )
K15
=INDEX(B$8:B115,MATCH (ROWS ($C$8:$C15) ,$J$8:$J$108,0) )
L15
=INDEX(C$8:C115,MATCH (ROWS ($C$8:$C15) ,$J$8:$J$108,0) )
M15
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C15) ,$J$8:$J$108,0) )
K16
=INDEX(B$8:B116,MATCH (ROWS ($C$8:$C16) ,$J$8:$J$108,0) )
L16
=INDEX(C$8:C116,MATCH (ROWS ($C$8:$C16) ,$J$8:$J$108,0) )
M16
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C16) ,$J$8:$J$108,0) )
K17
=INDEX(B$8:B117,MATCH (ROWS ($C$8:$C17) ,$J$8:$J$108,0) )
L17
=INDEX(C$8:C117,MATCH (ROWS ($C$8:$C17) ,$J$8:$J$108,0) )
M17
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C17) ,$J$8:$J$108,0) )
K18
=INDEX(B$8:B118,MATCH (ROWS ($C$8:$C18) ,$J$8:$J$108,0) )
L18
=INDEX(C$8:C118,MATCH (ROWS ($C$8:$C18) ,$J$8:$J$108,0) )
M18
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C18) ,$J$8:$J$108,0) )
K19
=INDEX(B$8:B119,MATCH (ROWS ($C$8:$C19) ,$J$8:$J$108,0) )
L19
=INDEX(C$8:C119,MATCH (ROWS ($C$8:$C19) ,$J$8:$J$108,0) )
M19
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C19) ,$J$8:$J$108,0) )
K20
=INDEX(B$8:B120,MATCH (ROWS ($C$8:$C20) ,$J$8:$J$108,0) )
L20
=INDEX(C$8:C120,MATCH (ROWS ($C$8:$C20) ,$J$8:$J$108,0) )
M20
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C20) ,$J$8:$J$108,0) )
K21
=INDEX(B$8:B121,MATCH (ROWS ($C$8:$C21) ,$J$8:$J$108,0) )
L21
=INDEX(C$8:C121,MATCH (ROWS ($C$8:$C21) ,$J$8:$J$108,0) )
M21
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C21) ,$J$8:$J$108,0) )
K22
=INDEX(B$8:B122,MATCH (ROWS ($C$8:$C22) ,$J$8:$J$108,0) )
L22
=INDEX(C$8:C122,MATCH (ROWS ($C$8:$C22) ,$J$8:$J$108,0) )
M22
=INDEX(F$8:F$108,MATCH (ROWS ($C$8:$C22) ,$J$8:$J$108,0) )
Excel tables to the web >> Excel Jeanie HTML 4
My spreadsheet inputs values 1,2,3, or 4 from a validated cell.
If the value =1 it needs to do the following:
IF I3 = 1, index (B23:B48, MATCH (J6, A23:A48,0)
But if it is 2 then it needs to do
IF I3 = 2, index (E23: E48, MATCH (J6, D23, D48,0)
But if it is 3 then it needs to do
IF I3 = 3, index (H23: H48, MATCH (J6, G23: G48,0)
and if it is 4 then
index (J23: J48, MATCH (J6, I23: I48,0)
I want to put these into one formula in a single cell. It should return one value that it has selected.
Anyone got any ideas on how to combine these please?
Hi all,
I've got a drop down list which is linked to a source sheet called "Mapping", and on this sheet I have selected my range for Data Validation for a Drop Down list.
However, there are certain values which are repeated, so I've used some IF, COUNTIF, ROWS and INDEX formulas to sort it out and remove duplicate entries.
In the table, below, the data is stored in Column C, the first part of my calculations is in Column A, and then the second part is in Column B  which I want to use as the source for my drop down list. The only problem that I have is that instead of blanks cells there are 0s in the cell as below. Could someone help me fix it? (cell C2 is intentionally blank)
Mapping
*
A
B
C
1
*
*
Entity
2
*
*
*
3
1
210
210
4
1
220
210
5
1
230
210
6
2
240
220
7
2
250
220
8
3
0
230
9
3
0
230
10
3
0
230
11
4
0
240
12
5
0
250
13
6
0
*
14
7
0
*
15
8
0
*
16
9
0
*
17
10
0
*
18
11
0
*
19
12
0
*
20
13
0
*
21
14
*
*
22
15
*
*
23
16
*
*
24
17
*
*
25
18
*
*
26
*
*
*
27
*
*
*
Spreadsheet Formulas
Cell
Formula
A3
=A2+IF(COUNTIF(C$3:C3,C3)>1,0,1)
B3
=IF(ROWS(A$3:A3)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A3),A:A,0)))
A4
=A3+IF(COUNTIF(C$3:C4,C4)>1,0,1)
B4
=IF(ROWS(A$3:A4)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A4),A:A,0)))
A5
=A4+IF(COUNTIF(C$3:C5,C5)>1,0,1)
B5
=IF(ROWS(A$3:A5)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A5),A:A,0)))
A6
=A5+IF(COUNTIF(C$3:C6,C6)>1,0,1)
B6
=IF(ROWS(A$3:A6)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A6),A:A,0)))
A7
=A6+IF(COUNTIF(C$3:C7,C7)>1,0,1)
B7
=IF(ROWS(A$3:A7)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A7),A:A,0)))
A8
=A7+IF(COUNTIF(C$3:C8,C8)>1,0,1)
B8
=IF(ROWS(A$3:A8)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A8),A:A,0)))
A9
=A8+IF(COUNTIF(C$3:C9,C9)>1,0,1)
B9
=IF(ROWS(A$3:A9)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A9),A:A,0)))
A10
=A9+IF(COUNTIF(C$3:C10,C10)>1,0,1)
B10
=IF(ROWS(A$3:A10)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A10),A:A,0)))
A11
=A10+IF(COUNTIF(C$3:C11,C11)>1,0,1)
B11
=IF(ROWS(A$3:A11)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A11),A:A,0)))
A12
=A11+IF(COUNTIF(C$3:C12,C12)>1,0,1)
B12
=IF(ROWS(A$3:A12)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A12),A:A,0)))
A13
=A12+IF(COUNTIF(C$3:C13,C13)>1,0,1)
B13
=IF(ROWS(A$3:A13)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A13),A:A,0)))
A14
=A13+IF(COUNTIF(C$3:C14,C14)>1,0,1)
B14
=IF(ROWS(A$3:A14)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A14),A:A,0)))
A15
=A14+IF(COUNTIF(C$3:C15,C15)>1,0,1)
B15
=IF(ROWS(A$3:A15)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A15),A:A,0)))
A16
=A15+IF(COUNTIF(C$3:C16,C16)>1,0,1)
B16
=IF(ROWS(A$3:A16)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A16),A:A,0)))
A17
=A16+IF(COUNTIF(C$3:C17,C17)>1,0,1)
B17
=IF(ROWS(A$3:A17)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A17),A:A,0)))
A18
=A17+IF(COUNTIF(C$3:C18,C18)>1,0,1)
B18
=IF(ROWS(A$3:A18)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A18),A:A,0)))
A19
=A18+IF(COUNTIF(C$3:C19,C19)>1,0,1)
B19
=IF(ROWS(A$3:A19)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A19),A:A,0)))
A20
=A19+IF(COUNTIF(C$3:C20,C20)>1,0,1)
B20
=IF(ROWS(A$3:A20)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A20),A:A,0)))
A21
=A20+IF(COUNTIF(C$3:C21,C21)>1,0,1)
B21
=IF(ROWS(A$3:A21)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A21),A:A,0)))
A22
=A21+IF(COUNTIF(C$3:C22,C22)>1,0,1)
B22
=IF(ROWS(A$3:A22)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A22),A:A,0)))
A23
=A22+IF(COUNTIF(C$3:C23,C23)>1,0,1)
B23
=IF(ROWS(A$3:A23)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A23),A:A,0)))
A24
=A23+IF(COUNTIF(C$3:C24,C24)>1,0,1)
B24
=IF(ROWS(A$3:A24)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A24),A:A,0)))
A25
=A24+IF(COUNTIF(C$3:C25,C25)>1,0,1)
B25
=IF(ROWS(A$3:A25)>MAX(A:A), "", INDEX(C:C,MATCH(ROWS(A$3:A25),A:A,0)))
Excel tables to the web >> Excel Jeanie HTML 4
Thanks in advance.
I have two cells with a formula.
Column G (this was a formula that I was helped with on from this site earlier in the week. Thanks again!!)
=IF(A86="","",(IF(SUMPRODUCT(ISNUMBER(MATCH($I$53:$I$89,INDEX(DATA!$AB$3:$AV$39,MATCH('100314T'!A86,DATA!$A$3:$A$39,0),0),0)))=0,"Needed",H86)))
Column H
=IF(COUNTA(INDEX(M$53:S$90,MATCH(TRUE,ISNUMBER(MATCH($I$53:$I$95,INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),0) ,0)),0),0))>0,INDEX($I$53:$I$95,MATCH(TRUE,ISNUMBER(MATCH($I$53:$I$95,INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),0),0)),0) ), INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),MATCH(TRUE,ISNUMBER(MATCH(INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A $3:$A$39,0),0),$I$53:$I$95,0)),0)))
Ctl+shift+enter
I am trying to combine G and H so I only have one column. When I copy the formula from H into G and hit the ctl+shift+enter for the array I get an error. The error is pointing to the index in the if statement.
What I would like to get to.
=IF(A86="","",(IF(SUMPRODUCT(ISNUMBER(MATCH($I$53:$I$89,INDEX(DATA!$AB$3:$AV$39,MATCH('100314T'!A86,DATA!$A$3:$A$39,0),0),0)))=0,"Needed",IF(COUNTA(INDEX(M$53:S$90,MATCH(TRUE,ISNUMBER(MATCH($I$53:$I$95,INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),0) ,0)),0),0))>0,INDEX($I$53:$I$95,MATCH(TRUE,ISNUMBER(MATCH($I$53:$I$95,INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),0),0)),0) ), INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),MATCH(TRUE,ISNUMBER(MATCH(INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A $3:$A$39,0),0),$I$53:$I$95,0)),0))))))
Ctl+shift+enter
I can do each else statement separate and it works but when I add the complex if part it fails.
Add the part if the if statement in H is true
=IF(A86="","",(IF(SUMPRODUCT(ISNUMBER(MATCH($I$53:$I$89,INDEX(DATA!$AB$3:$AV$39,MATCH('100314T'!A86,DATA!$A$3:$A$39,0),0),0)))=0,"Needed",INDEX($I$53:$I$95,MATCH(TRUE,ISNUMBER(MATCH($I$53:$I$95,INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),0),0)),0)) )))
Ctl+shift+enter
Add the part if the if statement in H fails.
=IF(A86="","",(IF(SUMPRODUCT(ISNUMBER(MATCH($I$53:$I$89,INDEX(DATA!$AB$3:$AV$39,MATCH('100314T'!A86,DATA!$A$3:$A$39,0),0),0)))=0,"Needed",INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),MATCH(TRUE,ISNUMBER(MATCH(INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$ A$3:$A$39,0),0),$I$53:$I$95,0)),0)))))
Ctl+****+enter
I even tried starting small.
Do a generic if statement and it works.
=IF(A86="","",(IF(SUMPRODUCT(ISNUMBER(MATCH($I$53:$I$89,INDEX(DATA!$AB$3:$AV$39,MATCH('100314T'!A86,DATA!$A$3:$A$39,0),0),0)))=0,"Needed",IF(10>0,H86,"not works"))))
When I add the true or fail statement it starts to blow up.
=IF(A86="","",(IF(SUMPRODUCT(ISNUMBER(MATCH($I$53:$I$89,INDEX(DATA!$AB$3:$AV$39,MATCH('100314T'!A86,DATA!$A$3:$A$39,0),0),0)))=0,"Needed",IF(10>0,H86, INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A$3:$A$39,0),MATCH(TRUE,ISNUMBER(MATCH(INDEX(DATA!$AB$3:$AV$39,MATCH(A86,DATA!$A $3:$A$39,0),0),$I$53:$I$95,0)),0))))))
Is there a restriction on the number of indexes and matches in an if statement? Or am I just forgetting something?
First, I want to say Thank You again to everybody for all the help & patience. Is there a source somewhere with sample formulas? The formula wizard is only helpful for single functions, not if I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.
I got some great help on indexing a range from another spreadsheet, but when I tried to use it again on a different range & copied & pasted and changed the ranges, I get #REF!, which I know means I screwed something up, I'm just not sure what & I don't know what the 1's & 0's mean.
=INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))
Thanks once again. If any of the guru's need help with their golf game, let me know & I'm happy to reciprocate!!!!!
Hi I have currently two formulas which work but I want to combine them into one.
the first ranks some values
the second matches and indexes to get a value
Both work on their own but when i try to combine the two I get an #N/A error
Here is my play sheet and some data along with the formulas I am using...
Index
Rank
Formula used
Retailer 1
1.251
2
=RANK(C158,C$158:C$166)
Retailer 2
1.119
3
Retailer 3
1.268
1
Retailer 4
0.932
5
Retailer 5
0.908
6
Retailer 6
1.063
4
Retailer 7
0.361
8
Retailer 8
0.358
9
Retailer 9
0.613
7
Result
formula used
Top 1st
Retailer 3
=INDEX($B$6:$B$14,MATCH(1,$G$6:$G$14,0))
Top 2nd
Retailer 1
Bottom 2nd
Retailer 7
Bottom 1st
Retailer 8
here is the formula I tried
=INDEX($B$6:$B$14,MATCH(1,RANK(C6,C$6:C$14),0))
Can anyone see what I'm missing
Hey all, in the process of helping a user on the forums here, I am building a megaformula that uses several INDIRECTs along with INDEX/MATCHes... however I'm running into a peculiar issue.
I have the following formula, which errors when I hit CTRL+SHIFT+ENTER on the highlighted part, saying that "The formula you typed contains an error":
=SUM(IF(MOD(COLUMN(INDIRECT("[all_sheets.xls]astork!"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100, MATCH ($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106104+$C$1*2)&":"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100,MATCH($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106))),2)=0,INDIRECT("[all_sheets.xls]astork!"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100,MATCH($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106104+$C$1*2)&":"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100,MATCH($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106)),""))
However, if I take the contents of that first indirect:
"[all_sheets.xls]astork!"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100,MATCH($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106104+$C$1*2)&":"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100,MATCH($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106)
and put it into a different cell, it returns the desired reference. So when I alter the original formula to just reference to this new cell, the formula works a charm. (The redhighlighted part is the only change, and that cell is the result of the formula just mentioned)
=SUM(IF(MOD(COLUMN(INDIRECT( $U$3 )),2)=0,INDIRECT("[all_sheets.xls]astork!"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100,MATCH($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106104+$C$1*2)&":"&ADDRESS(ROW(INDEX([all_sheets.xls]astork!$A$4:$A$100,MATCH($A4,[all_sheets.xls]astork!$A$4:$A$100,0))),106)),""))
Any idea what is going on here?
I am trying to build a formula by adding "=" and the text of an Index,Match,Match formula. The latter is stored on a separate sheet and I am building the formula on another. I have tried a number of ways with no luck. The cell ends up showing =(Index, Match, Match) rather than running the formula.
Thanks for your help.
Eric
Hello!
I am looking for a formula that can keep looking for cells after matching one criteria. Basically, the formula I have is only finding the first name that matches my search, but there are several persons who match the criteria.
=INDEX($D$8:$D$160,MATCH(L8,$E$8:$E$160,0))
Column L has numbers sorted from largest to smallest (eg. 550, 300, 289, 289, 250, 225, 225, 225, 150)
I would like Column J to find the name associated with that number, however, as you can see, some people can have the same number.
this formula is only returning one name, but I want it to find anyone else who has the same number as well.
I have used COUNTIF in the past, but I'm not sure how to integrate it into this current formula (I tried in front, and instead of the 0)
I have also used INDIRECT in conjunction:
{=INDIRECT("'sheet 1'!D"&SMALL(IF('sheet 1'!$F$7:$F$105=$H9,ROW('sheet 1'!$F$7:$F$105)),COUNTIF($H8:$H14,$H8)))}
I am not sure why this formula worked, as I tried to substitute this formula into my new spreadsheet which did not work, thus the new index and match attempt.
Any help is much appreciated thanks!
Mar
My brain is frozen right now and I cant figure out what I am doing wrong.
I want to look up value week 1, match the person, and return the value. From there I was going to do some addition and dividing of diffrent columns. Here is the chart:
week1
LECKENBY, MCKINSEY
45
468.77
24.17
444.6
419.57
82
week1
ARTHUR, YEVETTE
40
1837.47
108.25
1729.22
1627.66
367
week1
JARMIN, SARA
13
1514.83
71.39
1443.44
1355.49
281
This formula gives me the sum and division I want, but it does not look up week1
=(INDEX($B$3:$M$31,MATCH($P22,$B$3:$B$31,0),4)+INDEX($B$3:$M$31,MATCH($P22,$B$3:$B$31,0),6))/INDEX($B$3:$M$31,MATCH($P22,$B$3:$B$31,0),7)
For some reason I can't get this formula to return a value, it is just a #ref error
=VLOOKUP(Q21,$A$3:$H$31,INDEX(B3:H31,MATCH(P22,B3:B31,0),4),1)
Any ideas how to combine the 2 formulas into one would be great.
I am using Windows 7 and Excel 2010
Thanks
I'm in the process of redoing a spreadsheet as it runs very slowly. I'm after advise on which formulas are the quickest/slowest as the spreadsheet contains a fair number.
1. Should I be using vlookup or index/match?
2. I have a number of sumproduct formulas are these quicker than using sumif?
3. I use indirect in some formulas which picks up a sheet name. There are 12 sheets which are identical in format. Would it be better to paste the data from the appropriate one into a master sheet. I can then use a simple formula rather than including the indirect formula.
This is an example of one of the formulas I'm using
HTML Code:
=IF(Options!$E$9="Summary",1,INDEX('Fill'!$B$5:$CC$24,MATCH($B$7,'Fill'!$A$5:$A$24,0),MATCH(C$5,'Fill'!$B$3:$CC$3,0)))
Any advise on which are the quickest/slower formulas would be appreciated.
Hello all,
I have a excel sheet which allows me to select a rep in cell G2 and then I use Index/Match formulas to help me find all the data on that person. The problem i am having is that my index formulas are not working correctly. Either it gives me N/A# or wrong info.
Here is my formula:
=INDEX(Sheet1!A$2:A25,MATCH($G$2,Sheet1!$B$2:$B$250,0))
I have attached my excel sheet as well.
Thanks
Atm I am trying to get the following formula to work:
=IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=7,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))),M$2:N$10002,2),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=6,VLOOKUP(MID($ A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),L$2:N$10002,3),IF(INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))=5,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),K$2:N$10002, 4),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=4,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN( B31:E31),B31:E31,0))),J$2:N$10002,5),"ABC"))))
I wont bore you with what i am trying to acheive & why, but the last part of the formula there is a parathesis error apparently. Even though the Brackets all match up imo. ]=
when building IF formulas I usually leave the last error part as "ABC" which i then ichange when entering the next stage.
FYI: =IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=7,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))),M$2:N$10002,2),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=6,VLOOKUP(MID($ A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),L$2:N$10002,3),IF(INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))=5,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),K$2:N$10002, 4),"ABC"))) works. however when changing the "ABC" here, trying to insert:
IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=4,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31 :E31),B31:E31,0))),J$2:N$10002,5),"Check Manual")
I get my error msg,..,. pls someone tell me whats going wrong I Hva ebeen staring at this for hours.
Ty
SP
Hi i really need help, this formula basically represents a multiple lookup function. So I and looking for a code in 1 of 3 different collumns. But it just seems like to much overkill? Any suggestions for shortening it? Would really appreciate it.
#IF(ISERROR(IF(ISERROR(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$D$6:$D$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$E$6:$E$21,0))),IF(ISERROR(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$D$6:$D$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0)))))#
this relates to a post from a couple of weeks ago
http://www.excelforum.com/excelgene...ml#post2037422
Ok,
I have two workbooks. For this example we will call one workbook data.xls and the other is results.xls. The data.xls has 13 tabs (one for each month and total tab YTD). I want to use results to pull data from certain spots on data.xls.
Clear as mud yet?
I attached sample results on this thread.
I want to use a formula to pull data from the table based on index/match but want to use indirect. Here is my index function:
=INDEX([data.xls]Jun!$B$2:$D$4, MATCH(A2,[data.xls]Jun!$A$2:$A$4,0),MATCH(A1,[data.xls]Jun!$B$1:$D$1,0))
Now, I do not want data.xls AND/OR Jun to be static. I want to use something like this:
=INDEX(["&A3&".xls]Jun!$B$2:$D$4, MATCH(A2,["&A3&".xls]Jun!$A$2:$A$4,0),MATCH(A1,["&A3&".xls]Jun!$B$1:$D$1,0))
OR
=INDEX([data.xls]"&A$&"!$B$2:$D$4, MATCH(A2,[data.xls]"&A4&"!$A$2:$A$4,0),MATCH(A1,[data.xls]"A4&"!$B$1:$D$1,0))
SO my question is how do I use an indirect within my index/match???
I DO NOT want to use an indirect function that uses name ranges. I would need to name ranges constantly and would take away from my "automation" of this data entry spreadsheet. Anyone got any ideas? I will use an INDIRECT if I knew how to use indirect/match to find ranges of cells vs defined names...?
Thank you in advance to anyone who can help!

