Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Combine Index+match Functions With Indirect Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I need some heads up on how to combine some formulas. Here is my formula:


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!

View Answers     

Similar Excel Tutorials

How to Use the IF Function in Excel
The IF statement is a simple yet powerful tool. Today we will go through how the IF function works and some useful ...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
How to combine a list of data into one cell while putting a delimiter between each piece of data. This tutorial us ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...

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
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
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
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

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.

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:
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!

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.
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! First time user and poster. I've searched and seen several "indirect" functions but I still can't get a formula that accomplishes my goal.

This formula works for pulling info off one worksheet:

Now, I am trying to make this same formula a "sum" function over a range of worksheets (without having to simply add the same string above with each sheets name). The spreadsheets have to be index/matched because my two match criteria, month and account, are in different cell references across spreadsheets.

Any help would be appreciated!



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:


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:
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.

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))))

Good Evening,

I currently have a spread sheet which has an editable field in C3, B3 & E3 are populated by an index match formula to Tab 2, the formula is =IF(C3<>"",INDEX('2'!AG:AG,MATCH(C3,'2'!B:B,0)),"") for B3 and =IF(C3<>"",INDEX('2'!AO:AO,MATCH(C3,'2'!B:B,0)),"") for E3
I now require A3 to perform an index match against the data in B3 to Tab 1 this is my current formula =IF(C3<>"",INDEX('1'!B:B,MATCH(B3,Schedule!B:B,0)),"") but it doesn't appear to working correctly.

If anyone can figure this out it would be much appreciated.

I have attached a sample of my spread sheet to help

many thanks in advance


Hi there!!

The following is the problem I am encountering on excel 2010:

I have a quite complex worksheet and I named (static) some tables to scale down the complexity of the formula a bit.

In particular, in one sheet I have 25 tables (one for every company) and I named them accordingly. In another sheet I would like to be able to use an index match formula to retrieve some values from those tables I named (contained in the other sheet).

Now, no problem if in the index formula I write the name of the table as I created it, but I would like to make the process easier and create a reference to the name in another cell in the sheet. How can I do that?


Table name: "Company1" Sheet containing the table: "Sheet_Tables" Sheet containing the Index formula: "Sheet_Indexes" Formula that works: =index(Company1,match(...)

I would like to: - Use a cell in Sheet_Indexes (let's say cell A1) to create a reference to the table name, so: A1= Company1 - use the reference in the index formula to get to the table name instead of writing the full name of the table in every index formula, so: index(A1,match(...).

Does anyone know how to perform it?

I am trying with some indirect formula but it's not really working..

Thanks in advance!


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.



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.


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.


* 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


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 non-contiguous 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

Hello, I am trying to combine the Index/Match functions with the Concatenate Function.

I have two sheets (Sheet 1, Sheet 2). I want cell A1 in Sheet 1 to index and match a cell in Sheet 2. Sheet 2's name will change several times throughout the year, so I just want to be able to type the name of Sheet 2 into a cell B1 in Sheet 1 and have A1 reference that file name from the name in B1.

The Index/Match formula is simple (and works) if the file name for Sheet 2 stayed static. In this example, A1 is a color. It will reference the cell in Sheet 2 that matches "Fruits" and return to me the item in the Items Column.

Since the name is not static, I need to use the concatenate function to type in the new year and month (displayed as 2013.10 for October 2013) that each file is saved by. The file name would be "List 2013.10.xls" Next month it would be "List 2013.11.xls"

I came up with this formula but it is not working...can anyone tell me where I am going wrong?
INDEX("'"&CONCATENATE("C:\Files\Spreadsheets\",B1,".xls")!Items, Match($A$1,("'"&CONCATENATE("C:\Files\Spreadsheets\",B1,".xls")!Fruits,0))

In this, Cell B1 = "List 2013"

Can anyone help me here??

Thanks in advance

I've been using a long If formula in order to eliminate #n/a values:


Is there a shorter way to elimate the if statement and do the following:

For each cell in selection
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.



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)


* 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!!)
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)))

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('10-0314T'!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))))))

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('10-0314T'!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)) )))
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('10-0314T'!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)))))

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('10-0314T'!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('10-0314T'!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?

Hi I am new to this forum so apologies if I am not posting in the right way.
I am trying to setup an formula to look for an invoice number across 10 bank transaction sheets (different currencies) and return the amount paid.
The formula needs to search the first currency sheet e.g. gbp, then the next e.g usd etc etc and return the amount paid if it finds the invoice number.

The IFERROR works referring to 2 sheets but I am having trouble adding any more sheets to it. $A$3:$F$10 is the range on each bank sheet to search


I have tried adding ,IFERROR (obviously changing the sheet refs) and &IFERROR as well but it keeps saying error or too many arguments.


This is probably really simple but any help is much appreciated.

Many thanks

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.


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...

Formula used Retailer 1 1.251
=RANK(C158,C$158:C$166) Retailer 2 1.119

Retailer 3 1.268

Retailer 4 0.932

Retailer 5 0.908

Retailer 6 1.063

Retailer 7 0.361

Retailer 8 0.358

Retailer 9 0.613

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

Can anyone see what I'm missing

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.



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.


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!


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.

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


For some reason I can't get this formula to return a value, it is just a #ref error


Any ideas how to combine the 2 formulas into one would be great.

I am using Windows 7 and Excel 2010