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

http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups

----= East and West-Coast Server Farms - Total Privacy via Encryption =----

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

http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups

----= East and West-Coast Server Farms - Total Privacy via Encryption =----

- LookUP problem - MATCH & INDEX with OFFSET may help !!
- Match, Index, Indirect ?
- Need to Extract data from a row using Lookup, Offset, Match, Index(?).
- Index Indirect Match problem
- Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates
- Problem with INDEX, INDIRECT, MATCH, MATCH
- Index, Indirect, Match
- Index Indirect and Match Formula Question
- Match / index to lookup any data greater than 0
- Need help with replacing indirect function in a vlookup
- Match/Index or VLOOKUP in VBA?
- Need help with MATCH, INDEX or VLOOKUP
- VB Macro for Match Index
- Challenge! - match/index/lookup with multiple criteria
- Match Index
- Four criteria MATCH INDEX lookup of date between matching two text
- Lookup/match/index
- Return multiple column data from match index
- Lookup/match/index
- How to incorporate IF and VLOOKUP formula to a Match Index Formula?
- Match Index Lookup
- Match Index or Vlookup formula question
- Match& Index and Sumif
- Match/Index Returning #N/A

I have a table in one sheet in my xls which needs to lookup the second table -

TABLE ONE :

******** ******************** ************************************************************************>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=

ABCD1N.American31*Aug*0430*Sep*0431*Oct*042F08/04***3(Bid)***4(Offer)***5F09/04***6(Bid)*1,006.040*7(Offer)*1,010.010*Sheet8*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

TABLE TWO:

******** ******************** ************************************************************************>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=

BCDEF2Share*Class*Allocation31*Jul*0431*Aug*0430*Sep*0431*Oct*043F08/04****4(Bid)**1005.09301030.79005(Offer)**1009.02601034.87006F09/04****7(Bid)**1006.04301021.40408(Offer)**1010.00201025.4430Sheet9*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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

Stuart

=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,

Paul

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'm probably being thick, but I can't get the following formula to work:

=INDEX(INDIRECT($A$6),MATCH($A$7,$A$21:$A$24,0),MATCH(B$6,$B$20:$M$20,0))

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.

Help!

Steve

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

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

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?

=INDEX(INDIRECT("Term"&G2),MATCH(H2,INDEX(INDIRECT("Term"&G2),0,1)),MATCH(I2,INDEX(INDIRECT("Term"&G 2),1,0)))

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

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.

Example.xlsx

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:

=VLOOKUP(C4,INDIRECT(N7),2)

Cell C4 is a value greater than 1.

The formula in cell N7 is:

=VLOOKUP(NUM,DATA,7)

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

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.

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?

Thanks!

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

A2:8002

A3:8003

....

....

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

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?

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:

A B

01 x123

02 y456

01 x123

in this e.g, the formula use in A2

=INDEX(A$2:A2,MATCH(B2,B$1:b2,FALSE))

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

if_no_match_carryout_other_formula?

hope that i am clear..

pls advise

--

cjjoo

------------------------------------------------------------------------

cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916

View this thread: http://www.excelforum.com/showthread...hreadid=478698

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 dd.mm.yyyy. Text is formated as "General".

Sheet1: (Letters P, X, Y, AI are col. headings)

P X Y AI

Status code Cell name Start date Finish date

Assembly Cell1 01.10.2008 31.10.2008

Sheet2: (Results in B2 etc.)

A B C D

Running dates Cell1 Cell2 Cell3

30.09.2008 0 0 0

01.10.2008 1 0 0

02.10.2008 1 0 0

--

Thanks

John_J

match, index, vlookup...to 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.

--

Jules

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?

VB:buildsheet() 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 Else rngcell.Value = "no match" End If Next rngcell Set wssource = Nothing End With End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

match, index, vlookup...to 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.

--

Jules

FORMULA = INDEX(D:D,MATCH(1,INDEX((F2=E:E)*(D2=B:B),0),0))

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?

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

etc

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

--

Kevin

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.

Thanks

Ian

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

nothing.

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

appreciated.

-Sern