Combine Index+match Functions With Indirect Formula 


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!
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
INDEX INDIRECT MATCH functions for Payroll
 See how to do a complicated Payroll formula when tax data must be retrieved from multiple tables. See how to use the INDEX, INDIRECT AND MATCH functio ...
Two 2 way lookup with VLOOKUP & MATCH
 See how to do Two 2 way lookup with VLOOKUP & MATCH functions. Two way lookup can be done at least three ways with formulas that include:
1) ...
1) ...
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 ...
Two 2 W ...
INDEX function & MATCH function 12 Unusual Examples
 INDEX function & MATCH function. See 12 examples for the INDEX and MATCH function:
1.Use INDEX, MATCH and SUMPRODUCT to retrieve a whole colu ...
1.Use INDEX, MATCH and SUMPRODUCT to retrieve a whole colu ...
Helpful Excel Macros
Name Worksheets Based on Cell Contents
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Combine Multiple Workbooks into One
 This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
 This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Replace Formulas with Values (For The Entire Workbook)
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
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.
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
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!
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)))
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:
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:
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
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.
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
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
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
=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
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?
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'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?
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!!!!!
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
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 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
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
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 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:
Any advise on which are the quickest/slower formulas would be appreciated.
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
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
=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
#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!
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!