Nest Match & Index Functions

Jun 1, 2008

I would like to use a custom function or excel formula, if already exists, that finds the text "12 months" and then returns the value of the column that holds that value.

View 9 Replies


ADVERTISEMENT

Match And Index Functions

Jul 2, 2006

I am working on a scheduling system and am trawling through some old notes of some two or three years. I came accross two formulas, which I must have used a while ago. they are a little more baffling that what I have recently used, but I must have referenced them for a reason. Normally I write down explanations of the formulas in blonde, so that I can understand their reasoning at a later date, however, I can find no reference to them. explain what they actually do in a logical fashion. I have been looking at this for four hours, filling in date ranges and cells that they refer to, but cannot make sense of them.

=MATCH(E6,Caljay) + (MATCH(E6,Caljay)=MATCH(E6,Caljay))

=INDEX(Caljay,F6,1)-(INDEX(Caljay,1)-MAX(INDEX(Caljay,F6,1),E6))

View 3 Replies View Related

Index And Match Worksheet Functions

Aug 29, 2009

I am learning to use the index and match worksheet functions.

I read through some examples and tried to set up a spreadsheet as attached.

For some reason, it is working only partially. For some cells the value is returning correctly and in some it is returing #ref.

View 2 Replies View Related

Embed/Nest Three Functions.

Oct 23, 2008

I've already nested INDEX and MATCH sucesfully

I wish to embed/nest (I'm not sure what the difference/ correct syntax is)

These Funtions:

1. "=INDEX($H$4:$H$9,MATCH(G14,$G$4:$G$9,0))"

2, =HLOOKUP(Arg!D10,Arg!$D$10:$DA$50,5,FALSE)


The HLOOKUP returns a text value that the Index and Match convert into a number. I wish this to be done in one cell - is this possible?

I'm using INDEX with MATCH because HLOOKUP can return a 'not applicable' that equals '6' this need to be discounted in my results but that is a digression FYI.

View 2 Replies View Related

Nest Lookup With IF AND Functions

Jan 29, 2008

I have a spreadsheet that is by:

Purchase Date (column A)
Sales Date(column B)
Quantity(column C)
Name (column D)
Gain/loss (column E)

The spreadsheet is sorted by name in ascending order and also by quantity in ascending order.

Assuming row 1 is the heading. If D2 (name)= the same as another D cell and it's quantity i.e. C2 is negative, while the other D cell with the same name as D2 has a positive quantity i.e. corresponding C cell, and if the date in B2 is greater than date in column A of the other cell where it's D cell matches D2 and has a positive quantity, then in I would like "possible" to display in F2.

I have included an attachment to better illustrate what I have described above, because I am not sure if I am clear enough.

View 9 Replies View Related

Using INDEX / MATCH Functions With Duplicate Values

Apr 12, 2013

I receive data (A1:E2) which is sorted alphabetically accordng to name:

AaronBobConDanEd
3-Jan-135-Jan-133-Jan-131-Jan-132-Jan-13

I use the SMALL function to sort the data in date order e.g: A6= =SMALL($A$2:$E$2,1) = 1/1/13, B6= =SMALL($A$2:$E$2,5) = 2/1/13, etc:

1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13

I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX($A$1:$E$1,MATCH(A6,$A$2:$E$2,0)) = "Dan", B7= =INDEX($A$1:$E$1,MATCH(B6,$A$2:$E$2,0)) = "Ed", etc:

1-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
DanEdAaronAaronBob

The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".

How to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.

The formulas always assume the data is in multiple rows rather than multiple columns.

View 2 Replies View Related

Index And Match Functions In Macro Code

May 26, 2008

I want to write the following function using VBA. =index(B2:D8, match(lookup_value, A2:A8, 0), match(lookup_value, B1:D1, 0))

View 8 Replies View Related

Nest / Combine 2 Sumproduct Functions From 2 Different Tables

Aug 20, 2014

Looking to nest or combine the following 2 functions because I'm looking up the same cell content, except within 2 different tables.

=IF(B12="","",SUMPRODUCT(--($B$12:$B$17=X18),--($S$12:$S$17))) and =IF(B22="","",SUMPRODUCT(--($B$22:$B$32=X18),--($S$22:$S$32)))

Right now, these functions works well from a single table but need to combine them. Other function examples are welecomed too.

View 6 Replies View Related

Combine Or Nest Multiple Lookup Functions

Dec 16, 2006

I've got an indexmatch that works great
=IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($ A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accou ntstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))

But I need to incorporate IF statement based on varying levels of revenue and think VBA will be less cumbersome, but don't know how to combine else if and index match.

I am trying to accomplish this:
IF B$20 (which is a dollar amount is = X, then index, match....
IF B$20 is > Y but < Z, then index, match...

View 9 Replies View Related

Conditional Formatting With INDEX/MATCH Functions On Seperate Worksheets.

Apr 16, 2009

I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.

Worksheet (functions!)

This one has a list of numbers formatted as general. (Column G)

Is actually a formula/macro that outputs a number... (didn't know if this mattered?)

View 6 Replies View Related

Offset / Index / Match - Using 3 Functions To Search For And Return Values From Data Sheet

Jan 19, 2014

Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.

The attached spread sheet has performance data for a group of employees.

What I need to do is find a particular employee then return a value for one of the category's.

For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.

Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.

Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.

Maybe Offset-Index-Match is not even the way to go here?

View 7 Replies View Related

Excel 2011 :: Can't Get Index Match Max Functions To Work Sampling Data From Multiple Columns

Jan 29, 2014

I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.

I tried a few of the index match max formulas I found here and could only get them to work with one column of data.

I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.

I am using Excel 2011 for Mac

View 7 Replies View Related

Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately

Oct 8, 2009

I have this table

.......A.....B.....C....D
1.....I......a.....d.....g

2.....II.....b.....e.....h

3.....III....c.....f......i

As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i

I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on

I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.

View 9 Replies View Related

VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??

Oct 8, 2009

I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)

View 2 Replies View Related

Counting Number Of Times INDEX / MATCH Finds More Than 1 Match?

May 23, 2014

We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.

sampleexcelhelp.xlsx

The columns that need to be filled are shaded in dark pink.

View 5 Replies View Related

Index And / Or Match Formula With Multiple Match Required To Return A Value

Jan 21, 2014

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service
B = From
C = To
D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.

Additionally, if we were to add service as an additional lookup match how would this work?

View 3 Replies View Related

VLOOKUP / INDEX And MATCH To Return All Values That Match

Feb 4, 2014

I have two sheets of data , sheet A and Sheet B.

Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.

I have filled out the first two rows of results that should be returned as an example.

One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.

View 3 Replies View Related

Using Index / Match Function For Two Column Match Values

Aug 28, 2012

Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.

2011
2011
2011
2012
2012
2012

[code].....

View 5 Replies View Related

INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies View Related

Lookup/Index-Match-Match Using Segments

Jun 10, 2009

Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.

View 10 Replies View Related

Match Upper Closest Value Using Index-Match

Jan 15, 2013

I couldn't resolve the formula of Index-Match for getting the values that matches the following criteria

1) Match the "Category" of Table 1 to Table 2

2) Match the "value" of Table 1 to Table 2, if value is not present in Table 2 take the upper closest value.

Once the above conditions are met, Take the Result from Table 2

Table 1 is for Input and Table 2 is for Database.

Please note that Data in Table 2 should not be sorted in any way. I tried to use the -1 option of the Match function for upper closest value but it didn't work out.

Have a look at the file attached : IndexMatchUpperClosestValue_AlongwithExactMatch.xlsx

View 3 Replies View Related

Adding A Third Match To An Index Match Formula

Oct 6, 2009

I currently have a spreadsheet with two sheets. The first is my reporting sheet and the second is called Stores. I currently am using the following formula in the reporting sheet:

View 3 Replies View Related

Sum If W/ Index Match On Vertical And Horizontal Match

Sep 27, 2007

I am looking to sum the values found at the intersection of multiple values based upon a vertical and horizontal lookup. The formula I am trying is as follows (which results in #Value):

{=IF(ISBLANK(G$9),"",SUM(IF(INDEX('Journal'!$A$20:$Z$1020,MATCH("Prior",'Journal'!$F$20:$F$1020,0),MATCH(G$9,'Journal'!$H$19:$Z$19,0)),"")))}

View 9 Replies View Related

Nesting If And Match Functions

Sep 27, 2009

I've followed several tips on how to match these formulas, but somehow cannot make them work. I'm using Open office's version of excel. I need a formula that searches on 'tension downfall' for data in the first sheet, and then return a value corresponding to the row and column that match the input on the first sheet.

'tension downfall' shows a double entry table which shows amperage against length and returns a wire size. I would like to input amperage and length on two different cells and have the wire size needed for those values. Is it clear enough? I'm attaching my current work to make it a little bit clearer.

View 5 Replies View Related

Nesting Functions Of Match And Offset

May 31, 2007

Is it possible to nest the two functions of match & offset. I am working with two worksheets. One is a constant the other is building. I want to match a text from the first spreadsheet in the second sheet (otherwords I want to find that exact within the other spreadsheet) then I want to retreive the offset value to the right of the matched cell and place it in the constant worksheet where the formula is.

For Example

Sheet 1 Sheet2

Company: First Name: Company: Name:
Adamack's ? Adamack's Patrick

So if I put the formula where the ? mark is, I want to match the first company name on sheet 1, from the range in sheet 2, then I want to get the offset from that match 0,1 to give me the name patrick.

View 9 Replies View Related

Hlookup And Match Or Index And Match?

Apr 6, 2009

Example:......

On my worksheet 1, I have two dropdown boxes: B7 (that gives the building name) and B11 which gives the Type of Commission (Renewal, Expansion, New). B 20 is supposed to automatically select the percentage based upon what B7 and B11 select. E.g. B7 = House, B11 = Expansion then B20 should be 3.

This is my formula.
=HLOOKUP(B7,'Building Details'!$A$2:$R$24,MATCH(B11,'Building
Details'!$A$2:$R$2,0))

View 3 Replies View Related

Dynamic Range Using Offset And Match Functions?

Aug 26, 2012

I have three columns of data in columns A,B,C - there are formulas in these columns that either produce a numeric value or return blank.

I have created Dynamic Named Ranges for each of them and have entered the following formula:
=OFFSET(Working!$A$1,1,0,MATCH(1E+305,Working!$A:$A,1))

My issue is that this formula is looking one row past the last numeric entry in the column (a blank) which is leaving a blank space when I graph this data.

View 3 Replies View Related

Dynamic Named Ranges Using Match / Address And Indirect Functions

Dec 17, 2012

I am having an issue when I try a chart a named range. The named range "Refers To" is

Code:
=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(INDIRECT(ADDRESS((MATCH("Kevin",$A:$A,0)-1),1,1,1)
&":"&ADDRESS((MATCH("Kevin",$A:$A,0)-1),100,1,1))))

As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.

The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesn't flag up any errors, just nothing.

The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I don't want to have to keep changing the references in the graph so make sure the right data is being displayed. Is it just that charts don't play well with the INDIRECT function?

View 4 Replies View Related

IF INDEX And MATCH Together

Jan 8, 2014

I am using sheet 1 to pull data from sheet 2 using this formula

=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0)).

This on its own works to pull the data. However the data is repeated for each 'set' of data ie each group of repeating names, as I drag down. I just want the data to appear once on the first lie of each group so I have added an if to the formula

=IF(C2=C1,"No Match",

My complete formula is now

=IF(C2=C1,"No Match",=INDEX(Attendance!D:D,MATCH(C2,Attendance!A:A,0))

View 2 Replies View Related

Various Index Match

May 3, 2009

On the attached workbook, on the "Progress Summary" page, cell C30 has a formula which should list the names of pupils who made 0 progress (as referenced by the Raw Data page, column R [progress] and column A [name].)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved