# Excel 2011 :: Index / Match Finds 1st Incident / How To Find Last Instance

Nov 23, 2012

I am working on a data entry sheet time and I am running into a wall. I am trying to code intelligence into sheet so that the user doesn't have to enter as much data.

I am using Excel for Mac, 2011, 14.2.3, But I would like to be able to have others use it on PC and in Excel 97 - 2004

I am developing a time sheet for work. I am trying to have excel generate the work order # for subsequent jobs.

'Daily Entry Sheet'!\$B\$8:\$B\$138 = Date

'Daily Entry Sheet'!\$F\$8:\$F\$138 = Property #

'Daily Entry Sheet'!\$G\$8:\$G\$138 = Property Location

'Daily Entry Sheet'!\$H\$8:\$H\$138 = Work Order Number

Following is what I presently have coded.

The following Works but is not completely as I need.

=IF(OR(\$F8=0,\$F8="",ISNA(G8),\$H7="Work Order #"),"",IF(ISERROR(INDEX(\$H7:H\$8,MATCH(\$F8,Prop.,0))),"",INDEX(\$H7:H\$8,MATCH(\$F8,Prop.,0))))

This works fine in that the match finds the first instance of the work order #. The problem is there may be a later worker order # for the same property which supersedes the old work order. I need to find that last work order number for property # 919 for example, not the first instance, as the index/match returns.

I looked for a function similar to the sumif or countif

=SUMIF(Prop.,\$F4,WorkOrdNum)

=SUMIF(Date,"="&\$V6,Total_____Hours)

But I don't need to add the work order numbers.

Countif can give me how many instances property # 919 is used,

But I haven't figured out how to use that to It would be ok if the function would return the largest number like the MAX function, best would be to select based upon the latest date.

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

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

## Pull Every Instance INDEX/MATCH Criteria Into Consecutive Rows

Jan 11, 2010

I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student.

I've attached a mock report up so that maybe it would make more sense.

So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas.

Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs.

So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach.
Find the next instance Name & # matches, and pull that into rows 12-16, etc.

## Excel 2011 :: Conditional Formatting Based On Text Match From If Statement?

Oct 2, 2013

I want to highlight a cell if the text displayed from an IF formula is equal to the cell content.

CELLS BK10:BN10 are merged and have entered into them the following text "SELL"

Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"

=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))

Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%

When I set up a CLASSIC Conditional Formatting using a formula (="If(\$BJ\$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?

I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.

## Excel 2011 :: How To Find ColorA And Replace It With ColorB

Aug 24, 2013

I have Excel MAC 2011 and my spreadsheet was created with multiple cell colors randomly throughout the spreadsheet.

I need to change all the royal blue colored cells to "no color" without changing the data contained in the cell. For example; Cells A5:A38 are blue, A39 is green, A40:A50 blue, A51:60 green, ... Cells B5:B15 are grey, B16:B20 are blue, B21 is yellow...

I can't find the "Advanced Options" for Find and Replace that might have allowed me to specify a color change.

## Can't Find File In Another Instance Of Excel

Oct 27, 2013

I have a workbook in instance1 named Workbook1 that can successfully refer to and open a file named data.txt in C:

I have a workbook in instance2 named Workbook2 that can't find the same data.txt file in C:

Why can one instance find the text file but the other can't? How do I work around this issue?

Note: Workbook1 and workbook2 are identical except for their name

Note: Both workbooks are confirmed to be in (and are isolated in) their respective instances

## Index Find Next Match

Nov 3, 2008

i am trying to find all the 0 in column P and return col M from the same rows, this finds the 1st, how do i find the rest?

=INDEX(M\$3:M\$100,MATCH("0",P\$3:P\$100,0))

## Using Index / Match In VBA To Find Cell Address Not Value

Jul 1, 2014

I have a data table, called Table1 in Sheet1. In a simple form it is

Customer ID
Comment

A
asdf

B
jkl;

So in VBA, I need to figure a way to give me the cell address of the matching cell in Comments. So say Customer ID starts at A1 ... that means the Comment for Customer A is B2.

How would I get that reference in VBA?

## Find Number In Array That Is Less Than INDEX MATCH?

Jun 25, 2014

Cell P14 = 4.7

My array is H42:N72, in H42:H72 I have numbers 15,14,13....0,-1,-2,-3, etc...

In N42:N72 there is a corresponding \$ amount that I would like to return based on finding the value which is greater than 4.7 (so, looking to match the \$\$ amount to the number 5 in column H42:H72)

I've tried using INDEX MATCH but can't figure this out.

## Find Value In Range With Two Criteria - INDEX MATCH MAX

Mar 28, 2012

I am struggling with a INDEX,MATCH and MAX formula. I am trying to retrieve values from column E with two criteria:

1. That the value in column C is equal to a lookup value
2. That the date in column A is the closest before date for a lookup date

I have attached an example file.

## Match And Index To Find A Value Within A Named Range

Nov 12, 2008

Is it possible to use Match to find a value within a Named range and then, based on that value, use Match and Index again to find a value two columns across?

I have a list of clients in Column A, with 10 cells between each. In Column B, I have a list of currencies (the same currencies next to each client) and in Column C the rate this client pays for this currency. I want to reference these rates from an external workbook. Is there any way to use Match to locate the client name, then use Index/Match to locate the rate for a particular currency, somehow telling Excel where to look the second time?

## Use Index / Match To Find A Value In Column But Ignore One Value

Mar 13, 2013

I'm using Index/Match to find a value in another workbook, but there is one value that I want to ignore. For example here is my function --

=INDEX(ZZANALYSIS_PATTERN.xls!\$A:\$H,MATCH(D8,ZZANALYSIS_PATTERN.xls!\$G:\$G,0),2)

In D8, the value is Blue. Here is an example with made-up values (X is meaningless values) --

A B C D E F G
X Apple X X X X Blue
X Pie X X X X Blue

When I use the function, I want it to retrieve Pie, but it always retrieves Apple. Is there a way to ignore Apple? I've tried throwing "Apple" in there a couple different ways, but nothing is working. Or, if I can search from the bottom up, that would work as well.

## Using Index / Match / Mini To Find Three Lowest Values In A Row

Apr 22, 2014

I've used the below formula to fill column D with the name of the cheapest supplier for the parts listed in each row.

=INDEX(\$G\$1:\$M\$1,MATCH(MIN(G3:M3),\$G3:\$M3,0))

Is there a way to fill columns E & F with the 2nd & 3rd cheapest suppliers?

My table is shown below:

Capture.PNG

## Use Of MATCH And INDEX To Find First And Last Instances Meeting Criteria

Sep 30, 2009

I am trying to write a formula which finds the last instance of a number greater than 30 in a column (B). The values are not sorted from smallest to largest as they correspond to a time series (A) which needs to be preserved. Ultimately, I want the formula to return the time at which this value occurs, but I think I can do that bit using INDEX

I have a formula to give the first instance (row number) of a number above 30, which seems to work, but beacuse I don't fully understand how it works I can't modify it to give the last instance. The formula is:

=MATCH(1,INDEX(--(B2:B883>=30),0),0)

Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?

## Excel 2011 :: Find Multiple Text Strings In Another Text String

Mar 8, 2013

How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.

A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.

After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.

New words will be added to the sets of words continually as required.

Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.

I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.

The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.

If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.

Happy to consider a different solution if the task can be done better a different way.

Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.

## Conditional Formating IF, Match, Index And Vlookup To Find Several Duplicated Value

Apr 11, 2009

This had been hunting me for weeks and I still could not come over it after weeks of study the conditional formating IF, match, index, Vlookup, etc to find several duplicated value, e.g Column B = Name 1 & Row B = Time and so on, where as Name will have data of Name 1 duplicated in different time, while row B, "Time"would change to a different Name and maybe it would go back to Name one.

My problem was dont know which formula I can use in a different sheet to bring back the data to Name 1 did Task A at Time A, while Name 1 did Task B at Time K, etc. If this is not Name 1, then Name B replace above.

## Index Match Offset - Find Contents Of Reference Cell

Jun 21, 2012

How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G\$3) is equal to 9 I want it to use 8 instead.

Code:
IF(ISERROR(INDEX(tra!\$E\$2:\$E\$1100,MATCH(G\$3,tra!\$C\$2:\$C\$1100,0))),"",
INDEX(tra!\$E\$2:\$E\$1100,MATCH(G\$3,tra!\$C\$2:\$C\$1100,0)))

I tried using G\$3-1 but this doesn't appear to work.

## Index With Multiple Instance

Jan 30, 2014

I want to find top 2 performer in cell A17 & A17 in my attached sheet. Column B2:B13 changes everyday, so the name of performer changes accordingly. But today my top 2 performers achieved same but my formula is giving the same name.

Is there anyway to find out the other name i.e Ahmmed Shawkat Hossain here instead of Khan Md. Abdul alim again?

Attached File : Index 2nd name.xlsx‎

## VLookup Or Index Value Of First Instance?

Jul 6, 2012

I have data on two separate tabs. Data in columns A of both tabs potentially match. I am trying to identify and pull the value in column C of the second tab where the values in columns A match and the first instance where the value in column B on the second tab is less than the value in column B of the first tab. The values in both columns B are dates.

column A first tab = column A second tab
column B first tab < column B second tab (first instance of less than)
pull value from column C on second tab to appropriate row of column C on first tab.

## Implementing A Row Which Finds Rows Which Match It

Feb 1, 2008

I'm looking to have a row at the top of a worksheet which I can type in, so that only the rows below which contain that information will show up. For example, say I have the following 3 rows, 2 columns each:

Cat Feet
Dog Feet

I'd like to have an additional row so that if I typed in "Cat" only the "Cat Feet" and "Cat Head" rows would show up. Likewise, if I typed in "Head" in the proper column only ""Cat Head" would show up.

## Find Three Largest Numbers From A Variable Starting Index Match Point

Sep 1, 2013

My data is set out in columns, where alternate columns provide day numbers for given years (we can call these type 1 columns), with adjacent columns containing values which correspond to those type 1 column day numbers (we can call these type 2 columns). There are about a hundred columns in total (50 of each type). I would like to get excel to return the three largest numbers within each type 2 column, but I want to exclude data within the type 2 column above the point which is adjacent to a specific (varying) day number in the type 1 column. The location of this point varies for all the type 1 columns, according to a third row of numbers (the look up start point), which are currently listed below the dataset in every type 1 column. So, for the type 1 column "year 1", I would want Excel to ignore the values 0 and 1, which are listed next to day numbers 78 and 79, and begin looking for the three largest values down the column starting from the value which is adjacent to 81 (which is a 2). In type 1 column "year 2", excel would start looking for the largest values from the cell adjacent to 78, so it would ignore the 18 at the top of the column,and would return 2 and 12. And so on.

Year1
Value
Year2
Value
Year3

[Code]...

look up startpoint

81
78
62
83

## Conditionally Format Column B If It Finds A Match In A

Jan 30, 2012

What is wrong with this formula? I have used it before just fine, but sometimes including now it won't work.

PRODUCT_IMAGE Images 244-447439 244-455507 244-455507 244-455508 244-455508 244-455509 244-455509 244-455512
=MATCH(B2,\$A\$2:\$A\$412,0)

We can clearly see that we have a match in rows 2 and 1 columns A and B, but I am getting #NA. I have sorted the columns A-Z as well. I have also tried formatting the columns different ways and I still get #NA.

I need to conditionally format column B if it finds a match in A.

## Formula That Finds Matching Last Name Then Looks To Match First 3 Letters Of First Name

May 21, 2014

In Column A I have first names, In column B I have last Names, in column C I have id letters,
Column D another list of First Names And In Column E I have another list of Last Names

So what I need to do in F2 is Look at the name Last name in E2 (Lets say its Smith) then look down the Last names In Column B when you find a match look at the First name on the same row to see if the first 3 letters are the same as the first 3 letters in D2 if they are then put the id that's in cell C into F2 if not ""

I've been trying for hours but no luck, also if you do manage to do it can you tell me how you get it to look at the first 3 letters and how I could change that to 4?

## Range- Code Stop When It Finds The First Match

Aug 22, 2008

Sub do_it()
Dim x As Range
Set x = Columns(4).F ind("*test*", lookat:=xlWhole)
If x Is Nothing Then GoTo 999
r = x.Row
Cells(r, 1) = Cells(r, 1) & " (W)"
999
End Sub

I need the above code to continue down column 4 and add the " (W)" to the entire column.
The code stop when it finds the first match.

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

## Excel 2010 :: Index / Match Duplicate

Jul 18, 2014

I have a victim of the Index-Match duplication problem in Excel (2010). Basically, I have three columns of data, all daily input for the year.

Column 1 = Date
Column 2 = Actual (Units Sold)
Column 3 = Scheduled (Units Sold)

The Date is filled out through the end of the year as is the Scheduled values.
The Actual values are filled out daily.

I need to generate a summary box that reports Actual, Scheduled, and Variance (Actual - Scheduled) for the time periods Daily, Month to Date, and YTD.

My problem is that when I try to return the Schedule value that corresponds with the date of the last entry, I don't know if I am pulling the correct Schedule value since I do not know if the Actual value (that is pulled from the last value in the Actual column) is unique. So I tried using an Index-Match formula to return the latest value (that is the last record occurrence of the value) to my function in order to retrieve the correct Schedule value, but, sadly, it did not work.

I by no means am an Excel expert like many of you, so I may have some questions along the way.

I've attached a sample extraction from my worksheet and included an example of the Summary panel I'm creating.

ActualVsScheduled_problem - Copy.xlsx

## Excel 2010 :: Index Match Using Array?

Sep 13, 2012

The context of this problem is that I am pulling sales data from different outlets to analyse monthly sales. The data from the various outlets are in various formats and the products, even though they are the same throughout all the stores, are named differently. For example, Mushroom Soup can be named as "Soup Mushroom", "Mushroom Soup" or "Cream of Mushroom". This makes analysis really tough.

I first created a table with a list (K6:P6) that had the varying names of the products. This is shown as the first table with 3 rows.

Then I tried to match this list to a range of products sold by an outlet e.g. Electric. (shown in the next bigger table \$D\$10:\$D\$254, ) Next, I used Index to call up and display the sales of that product next to table with list A (\$G\$10:\$G\$254)

This works for some entries but not for all. I get mostly #NA answers even though I do ctrl-shift-enter. Also, the bigger table is in a separate file and I am using excel 2010.

=INDEX('[GON-JUN 2012.xls]gon-jun'!\$G\$10:\$G\$254,MATCH(K6:P6,'[GON-JUN 2012.xls]gon-jun'!\$D\$10:\$D\$254,0))

[URL]

## Excel 2010 :: Column And Row Match And Index

Oct 3, 2013

Whatever formula I try is returning an error of #N/A

I have two worksheets the first with transport data - customer,collection point, delivery point, haulier and number of pallets - all this information is in columns; the second sheet with the rates show haulier, customer, validity, delivery point in columns and then pallet numbers run across a row with rates tying in underneath. i have simplified this below.

I am just trying to reference all the data and work out what the cost for the variables would be in the example the cell i'm trying to find is H8 (320) -

I have already tried the following and several modified versions:
=INDEX('RATE LOOKUPS'!\$E\$6:\$I\$9,MATCH(1,('MASTER DATA'!A:A='RATE LOOKUPS'!C:C)*('MASTER DATA'!B:B='RATE LOOKUPS'!B:B)*('MASTER DATA'!C:C='RATE LOOKUPS'!D:D)*('MASTER DATA'!D:D='RATE LOOKUPS'!A:A)*('MASTER DATA'!E:E='RATE LOOKUPS'!E5:I5),0))

[Code] ..........

## Excel 2010 :: Index / Match With Reference?

Dec 24, 2013

I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).

When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.

**Why does it do that and is there a way to stop it?

Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.

I am using Excel 2010 on a company computer with the macros blocked so no VBA.

Code:
=SUM(IFERROR(INDEX(Mon!\$D\$6:\$D\$225,MATCH("2000",Mon!\$E\$6:\$E\$225,0)),0),
IFERROR(INDEX(Mon!\$D\$6:\$D\$225,MATCH("2000",Mon!\$N\$6:\$N\$225,0)),0),
IFERROR(INDEX(Tue!\$D\$6:\$D\$225,MATCH("2000",Tue!\$E\$6:\$E\$225,0)),0),
IFERROR(INDEX(Tue!\$D\$6:\$D\$225,MATCH("2000",Tue!\$N\$6:\$N\$225,0)),0),
IFERROR(INDEX(Wed!\$D\$6:\$D\$225,MATCH("2000",Wed!\$E\$6:\$E\$225,0)),0),
IFERROR(INDEX(Wed!\$D\$6:\$D\$225,MATCH("2000",Wed!\$N\$6:\$N\$225,0)),0),

Excel 2010