Free Microsoft Excel 2013 Quick Reference

Match, Index, Indirect, Offset

I often see these functions used together and used quite a lot but what
practical application do they have?

From reading the help files it is easy to understand the theory of what's
going on but applying it in practice is something else.

Sumproduct was a classic case in point here. Going by the help file, we are
told that sumproduct multiplies corresponding values in arrays and then sums
the result (yeah but so what). But who would have guessed from this that
it's application is quite extensive as a filtering tool of a sort.

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Post your answer or comment

comments powered by Disqus
I am trying to use a vlookup function but believe I need an INDEX and MATCH calculation with an OFFSET feature.

I have a table in one sheet in my xls which needs to lookup the second table -
******** ******************** ************************************************************************>Microsoft Excel - Menzies Oct04.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box


******** ******************** ************************************************************************>Microsoft Excel - Menzies Oct04.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

How do I get the first table to lookup the bid and offer values in the 2nd table dependent on the value in Cell C1 (the month) in the first table.
I need to OFFSET the row which is looked up to the ones below the identifier of F08/04 for the right BID and OFFER values.

Many thanks as this has been driving me nuts !!

I've got this formula that works if my lookup range is in ascending order.

=VLOOKUP(A41,INDIRECT("'[Name "& M$25 &" 07Analysis.xls]Overall Top
Ten'!$AA$2:$BJ$401"),MATCH($V$3,INDIRECT("'[Name "& M$25 &"
07Analysis.xls]Overall Top Ten'!$AI$1:$BJ$1"),0)+8)

I need some help modifying this so that I can acheive the same results if
the lookup range is not in order. I think it may involve INDEX and MATCH,
but I'm having a little trouble putting it together.

Can someone assist?

Thank you,

Please help.

My data looks like this:
A2:A6 = Account 1,2,3,4,5.
B1:F1 = Monday 1st Jan to Friday 5th Jan.
In between this data are random balances of $2000, $0, or nothing (empty cell). eg. Account 1 has balances of the following for its week:
-,2000,0,-,2000 (for Monday-Friday).

My request: I need to report the accounts that have been imbalanced for 3 days or more.
In more detail, how can I work out per account (1.) the current balance; (2.) the dates the current balance appeared, and (3.) the date that the last 0 appeared? I have used all manner of offset, match, index, lookup functions to work this out, but little luck so far.
I have worked out the current balance with this formula:
=LOOKUP(MAX(B2:F2)+1,B2:F2)The dates though, are proving difficult.
Any tips?

I come to you for help once again.

I'm probably being thick, but I can't get the following formula to work:


I've placed it in a work book to retrieve annual data based on selections from combo boxes but I can't get it to work!

I include the worksheet to show you what I'd like.



Hi all,

I have an issue with returning values from duplicates.

A: 10000 Names (They include both Characters and Numbers)
B: 1000 Prices (Only Numbers - Numerical Values, Non-Negative)

C: List of Prices
D: Return the Names

I want to search for a name by PRICE. Because the prices many times are repeated the vlookup stops at the first name it reads.

I have read somewhere on internet that with MATCH, OFFSET, MATCH, INDEX, COUNTIF I can create an array formula.
MATCH -> Finds the row which first value occurs
Offset -> It is used for the next match (N+1)
2nd MATCH --> which will offset the function and skips the row which contains the first match and will return a row number relative to the top of its array.
Index --> to read the table of data
Countif --> How many MATCH (down-counter) to count. When is 0, it is supposed that we have found all the values we requested at the beginning.

A sample formula which I started is this one =INDEX($A$5:$A$60,MATCH(C1,$A$5:$A$60,0)) but I dont know how to combine the things above to make it work. I have been looking around the forum and although I thought that I could find something, it ended up without success.

Any help would be much appreciated. Thank you

I have been unable to get the Indirect portion of a formula to work.

The following formula works but when I tried to include an Indirect Index all I get is #REF!

This is how I tried to add the Indirect portion.


Jim O


Hi, with formula above I am trying to pull data from a worksheet: name in C1. The problem is that it pulls ranges in the same master worksheet. Any suggestions?

Any idea why my formula isn't working exactly. The formula I'm using is


I'd like to be able to have the formual return a APR for the selected term, LTV, and credit band

Any help is appreciated!!!

Hey all,

Thanks for being so helpful on these forums, I am learning a lot. I have a question using match / index or vlookup to pull data from one worksheet to another. Take a look at the attached example it will hopefully make my explanation a bit more clear.

I have an input page where coworkers will input weights into column C as to what data they want to consider. What I want is for only the Metrics that are marked with a value >0 to be reflected in the output page. I figured out how to do this with the first metric marked with a value greater than 0, but I am struggling to figure out the remainder.

A few notes to keep in mind, I can't sort the data or use VBA (my boss lives in the dark ages and doesn't "trust them"). Also, I have a ton of data and metrics so I want something that is computationally not too intense. For that reason I am trying my best to avoid arrays or the indirect function.

Any help would be much appreciated.



I am using a VLOOKUP formula incorporating the INDIRECT function and want to replace the indirect function with something else, perhaps a match, index, choose, offset or some other function. I cannot figure out a way to write this formula with a different function and need help. Here is a link to my file.

The following formula in cell B7 is:


Cell C4 is a value greater than 1.

The formula in cell N7 is:


NUM is cell C2 and can be a value greater than 1. The contents of range name DATA is

1 Asample D E F G AAA
2 Bsample D E F G BBB

This table can have up to 100 rows and 50 different values in column 7.

The formula in N7 therefore returns AAA or BBB by matching the value of NUM in range DATA and looking up the contents of column 7. AAA or BBB are range names of other tables which are used in cell B7 thanks to the INDIRECT function. Using this function B7 does a lookup in table AAA or BBB.

Table AAA looks like this

0 5.75%
100,000 4.75%
250,000 3.75%
500,000 2.00%
1,000,000 0.00%

Cell B7 will return a value equal to =LOOKUP(C4,BBB,2). If cell C4 is equal to 125,000 then the value returned in B7 is 4.75%. I cannot hardcode the range name of the table because the user can change the value of NUM.

Any suggestions?

Thanks for your help!!

I want to use Match/Index or VLOOKUP in VBA to locate a cell in another worksheet and perform a calculation.

Sequence of Events:

-User will select a Component from a drop-down (Cell B5) and hit an "Overhaul Completed" button
-The button will look up the selected Component in another sheet where all components (A column) are listed with lots of data about them
-The corresponding cell for that component will be selected in the "Hours till Next Overhaul" column (C column).
-For this example, lets say that the cell selected is C5
-The formula for C5 should currently be B5-(PME!G1), where the value imported from PME! is the current engine hour value and it changes every month
- The formula should now change to B5-(PME!G1)-(PME!G1). But the second PME!G1 is going to a static number that will never change. So if the hours are at 14474 this month, the formula should be B5-(PME!G1)-14474.

This is what I have so far:
Dim rngRequested As Range
Set rngRequested = Sheets("Sheet10").Range("B5")
.VLookup(rngRequested.Value, Sheets("Sheet7").Range("A3:C21"), 3, False).Select

The select and vlookup functions are probably used incorrectly here.. So once I have that fixed, I can move onto the calculation part...

The reason why I might seem to be going a bit crazy on the automation is because I want to exploit the full capabilities of excel/vba to make the workbook idiot proof. There will be many operators using this book and our goal is to have them do a little as possible, while getting all sorts of valuable data.

I'm not sure if there is a way to do this - I've been trying to figure it out for 2 days but can't seem to get the MATCH/INDEX or VLOOKUP features to work correctly.

This is my challenge. I have two spreadsheets. The first includes customers with a single customer number in a column. On the second spreadsheet some customers have multiple customer numbers separated by commas. What I need to do is a search on the customer number in the first spreadsheet to see if it matches any of the customers in the second spreadsheet, if there is a match then return the customer service support name.

For example:

Spreadsheet 1

Column 1
Customer No = 1234

Column 2
Customer Name = ABC company

Column 3
Service Rep = (this is what I'm searching in the second spreadsheet, based on the second spreadsheet the answer would be John Doe)
Spreadsheet 2

Column 1
Customer No = 4534, 1234, 5643, 6546

Column 2
Customer Name = ABC Apples Corporation

Column 3
Service Rep = John Doe

I can't search on customer name because it may be different in the two spreadsheets so the only good value for me is the customer number but some customers have multiple numbers.

Any ideas/recommendations?


HI All,
I need your help to write a Macro which will match a string in all the
sheets and Index the corresponding values next to it.

Eg i have 4 sheets in a Workbook. in the sheet4 i have values

A1: 8001

and sheet1,sheet2,sheet3 as values like this

A1:7001 B1: Sold
A2:8002 B2:Not sold
A3:6005 B3: Not sold

Now in Sheet4, I want to search for values in range A:A(sheet4) in all the
other sheets in the workbook and Index the corresponding values in Range B:B.

I know how to use Match Index function, but i want to write a code what will
match the value in all the sheets in the workbook and retrieve the
corresponding value.

In addition to that.. I would also like to know the code snippet if i want
to run this code to retrieve values from other excel file and not from the
other sheets of the same file.

Hope I am able to express my query. Any help will be appreicated

I have a roster that I am trying to automate. I would like to be able to
place the date in a cell and in another cell return the correct crew for that
date. This is complicated by the fact we have two shifts per day, 3 crews
that alternate between these shifts and each date corresponds with a
different crew roster. This may explain it a bit better:

each line corresponds to a month, but the dates in column one do not start
with the 1st day of the month - dates correlate to days of the week
(perpetual calendar)

week crew mon tue wed
1 a ngt day day
b day ngt off (blank cell)
c off off ngt

2 a d off n
b n n off
c off d d

ditto for week 3

mon tues wed thurs
week 1 10 Jun 11 Jun 12 Jun 13 Jun
week 2 8 Jul 9 Jul 10 Jul 1 Jul
week 3 5 Aug 6 Aug 7 Aug 8 Aug

I would like to select a date in another worksheet, and in another cell have
the date on the above example recognised and return in this other cell the
corresponding crew and shift that they are on. eg. for 9 July the answer I
need is "b crew ngt shift"

Can anyone help please - I've tried lots of combos of match, index and
lookup and can't seem to get it to work?

just wanna ask about the use of match and index. in this scenario, i
want the cell to perform a match index and if there is a match, it will
return the value, if thee is no match, carry out another formula. This
is the scenario:

01 x123
02 y456
01 x123

in this e.g, the formula use in A2

but if there is no match, the cell will return a NA but now i would
like the cell to perform another formula if there is no match . Can it
be done using IF function?

something like if match, do_match(index),

hope that i am clear..

pls advise

cjjoo's Profile:
View this thread:

I got stuck after 2 criterias, can anyone help! I need a formula that returns
1 if true and 0 if false in Sheet2!B2:Z1000 (filled/copied out). The formula
shall return 1 if the assembly workcell is occupied on the dates in
Sheet2!A2:A1000, and 0 if not.

For the formula in Sheet2!B2:
Criteria1 and 2: Lookup value=Running dates Sheet2!A2 is between Lookup
range=Sheet1!Y2:Y1000 and Sheet2!AI2:AI1000
Criteria3: The text "Assembly". Lookup range Sheet1!P2:P1000.
Criteria4: The cell name Sheet2!$B$1. Lookup range Sheet1!:X2:X1000

I have read several of the MATCH INDEX threads on this forum, but I cant get
tit to work with 4 criterias combined with the "between dates". Dates are
formated as Text is formated as "General".

Sheet1: (Letters P, X, Y, AI are col. headings)
Status code Cell name Start date Finish date
Assembly Cell1 01.10.2008 31.10.2008

Sheet2: (Results in B2 etc.)
Running dates Cell1 Cell2 Cell3
30.09.2008 0 0 0
01.10.2008 1 0 0
02.10.2008 1 0 0


I have two worksheets...I need to match serveral criteria...I have tried
match, index, no avail.

I have to match the surgeon with the correct day and time his/her room is
available to the time in which they preformed the surgery.

so, surgeon block (time they reserve) with the time they do surgery (a
seperate worksheet) contianing surgeon, date, time of case.

anyone know what formula or function I can use?

Thanks os much for the time.

I am completely stumped on something that seems so simple. I have searched and searched and have yet to find code to solve this.

I have two worksheets (build and itrw01).
I am using match index to match buildsheet, col a to itw01sheet, col d, then copy the value from itw01sheet, col e to buildsheet col d.

the code works for this, but I really want to copy columns e,g,h,i to the build worksheet. This is where I fail. Any ideas?

    Dim vmatch2 As Variant 
    Dim rngcell As Range 
    Dim wssource As Worksheet 
    Set wssource = Sheets("itrw01") 
    Dim buildsheet As Worksheet 
    Set buildsheet = Sheets("Build") 
    Dim lastrowindex As Long 
    Dim lastrowlu As Long 
    With buildsheet 
        lastrowindex = Range("a65536").End(xlUp).Row 
         'lastrowlu = Range("e65536").End(xlUp).Row
        For Each rngcell In buildsheet.Range("d11:d" & lastrowindex) 
            vmatch2 = Application.Match(buildsheet.Cells(rngcell.Row, "a"), wssource.Range("d11:d" & lastrowindex), 0) 
            If IsNumeric(vmatch2) Then 
                rngcell.Value = Application.Index(wssource.Range("e11:e" & lastrowindex), vmatch2) 
                 'rngcell.Value = Application.Index(buildsheet.Range("a11:a" & lastrowindex), vmatch2)
                 'MsgBox rngcell.Address
                rngcell.Value = "no match" 
            End If 
        Next rngcell 
        Set wssource = Nothing 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have two worksheets...I need to match serveral criteria...I have tried
match, index, no avail.

I have to match the surgeon with the correct day and time his/her room is
available to the time in which they preformed the surgery.

so, surgeon block (time they reserve) with the time they do surgery (a
seperate worksheet) contianing surgeon, date, time of case.

anyone know what formula or function I can use?

Thanks os much for the time.

I've a Match Index formula:


And I want to incorporate this condition

If value of (vlookup(A2, D:F, 3, FALSE) is GREATER than zero(0) do FORMULA

If EQUAL or LESS than zero(0) go to next row then if EQUAL or LESS than zero again go to next row

If finally in the last row it gives GREATER than zero then do the FORMULA

Is this possible?

Hi - have done this in the past for unfortunately forget

I have a list of days in number format say from 365 in my table. I have set
up a list of then 12 mths so in excel I have in cell
A1 = 365, B1= 12mths
A2 = 335, B2 = 11mths

I want to lookup a value in my table and match it to my list so that for
example if the value is 360, the lookup would go though my list and realise
that 360 is less than 365 and skip to next value and then look to cell A2 as
as 360 is above the value in A1 it returns the value in B2.

I think it is quite a simple match / index type lookup (where you have to
have you values in a list with the highest value first in the list)

All advice much appreciated thanks

Hi all

I have an issue with a formula in excel, I can't seem to figure out which one to use, I seem to come close but can't quite figure it out.

In the attached file I've tried to replicate my problem but on a smaller scale.

I will be receiving data each month that looks like 'sheet2' Or I can also receive it in the format shown in 'sheet3' if that's easier to work with.

I want to get it so that it is in the format shown in sheet1, so I want to look up the respondent number row, look up the code column and place a '1' if that respondent matches the code. I can't use a pivot table as in 'sheet1' I will also have some sample information and there won't be the same codes every month.

I thought I had the perfect formula "=IF(VLOOKUP(A2,Sheet2!A:I,2,FALSE)=Sheet1!B1,"1",IF(VLOOKUP(A2,Sheet2!A:I,3,FALSE)=Sheet1!B1,"1"... ... etc etc" but it wouldn't let me nest more than 7 in a row or something like that! I also tried a match index but can't quite get my head around it hence why I'm asking for help.

If anyone can find a way I'd be really grateful! Sorry if I haven't explained myself clearly but if you have any questions just ask.


I have a spreadsheet with a worksheet that has all the budget plans and
another worksheet with summary YTD. What I would like to do is on my
summary sheet pull through the cumulative plan to date based on the
month entered in cell G5.
So far I've managed to identify which columns need to be summed based
on what is entered in G5, by using an IF statement. I then have a sumif
(in column D)which would sum all the values for the valid month in G5.
The problem I am having is now combing this with a lookup of some sort.

I think I need to use a match & index and sumif... but not sure how to
contruct this.
There may be a simpler way. Any help is appreciated.
Below is what I hope to achieve

User enters month number into G5
Column D is then updated...based on indicator in Column C, it shows the
cumulative YTD budget.


Been searching previous post for an answer to my question, but I got

Here's my problem:

I have two tables in two worksheets. Fist table consists of sizes,
broken down by their different types, i.e.

Fractional Letter WireGauge Metric

Then I have another table that consists of product categories. The
categories are determined by the product type and size, i.e.

Fractional Letter WireGauge Metric
Product type Cat1 Cat2 Cat3 Cat4

In a third worksheet I have a 32,000 row list of the products and their
different sizing. I need to categorize these products. The formula
that I thought would do it is

=Index(Table,MATCH(product,ProductList,0),MATCH(Index(SizeNames,OR(IF(MATCH(size,Fractional,0),1,0), IF(MATCH(size,Letter,0),2,0),IF(MATCH(size,WireGauge,0),3,0),IF(MATCH(size,Metric,0),4,0)),SizeList, 0+1)

I thought that by setting the value if false to 0, I would get the
value that corresponds to the correct size type, but instead it returns
#N/A. I can't think of anything that could fix it.

Hope I explained everything well enough. Any help would be


No luck finding an answer? You could always try Google.