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

View 9 Replies
View Related
Jul 11, 2014

im trying to look up data by a date and then between times.

I have wrote a formula in C6 but it does seem to work.

On the auto sheet, I need to lookup the date in cell B3 then handled in cell C3 and finally lookup the time in cell B6. Once I have this I need to look on the Data Tab in row one for the date then row 8 for the handled text and then finally I need it to look at column G to bring back the BETWEEN times.

View 3 Replies
View Related
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
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
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
Aug 7, 2009

I have the following formula that returns a number, however it returns the first number it comes to when the criteria is met,

View 2 Replies
View Related
Jun 22, 2009

in one column I am looking up the value Yes to return the row number.

=MATCH("Yes",C:C,0) in this case it returns a 2

I want to use this row number in a sum...

i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.

View 9 Replies
View Related
Jan 16, 2010

I have to do a vlookup in 12 sheets (named ranges)

I use the formula '=vlookup(a1;choose(1;range1;range2; etc);2;0)

In this case I have to change the 'choose index_num' every time to match.

Is it possible to do the lookup without changing the 'choose index_num'?

View 9 Replies
View Related
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
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
Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies
View Related
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.

View 10 Replies
View Related
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

Cat Head

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.

View 10 Replies
View Related
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
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
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
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
Aug 22, 2014

From the highlighted cells at B17 to B19, while using some kind of lookup or indexing method, I want to count the number of cells in that specific range that's great than zero. This list is just a sample, the list will be much bigger and order may not be the same--that's why I need to lookup the project name.

View 7 Replies
View Related
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.

View 9 Replies
View Related
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?

View 9 Replies
View Related
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.

View 9 Replies
View Related
Aug 5, 2014

I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.

In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.

View 6 Replies
View Related
Mar 21, 2014

I am trying to find a formula that populates a room number based on the number of hours used. I have a chart that is by building but multiple rooms within each.

Where I am having trouble is when there are more than one room with the same number of hours used then sometimes the formula populates the wrong room number.

Attached is my example spreadsheet of my chart. Book1.xlsx

I have shown what I need, what I have tried and a blank space to show where I need it to go.

View 3 Replies
View Related
Jun 6, 2006

I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)

View 3 Replies
View Related
Jun 24, 2009

I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.

View 4 Replies
View Related
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
Jun 25, 2014

Working on this formula:

=INDEX(INDIRECT($F$36&"!EB:EB"),MATCH(LOB!G41,INDIRECT($F$36&$G$36),0))),"")

Recently found out that INDEX MATCH will return the 1st value it matches. In this case it is looking up LOB!G41 in column DD!J:J but there are 3 occurrences of the value 75,000 and each belong to a different SKU. So, it's just returning the 1st one. Those are in column EB. I also have a column A, that has the row number.

how to make this find the next result. So, 75,000 - 1, 75,000 - 2 and 75,000 3.

I figure its some use with SMALL and Countif but I can't figure it out.

View 5 Replies
View Related
Jan 2, 2009

I built my own index/match function. It works, unless there is no match, then I get the #NA result. I want it to return a blank.

I tried the ISERROR and that converts it to true / false & it won't let me add "" to the end, like a general 'if' statement.

View 2 Replies
View Related
Nov 22, 2007

I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....

View 9 Replies
View Related