# 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

View 2 Replies
ADVERTISEMENT
Jan 19, 2007

i have in the range (Ag1:an1)the names of the months from january- august)in the range (Ag2:An55) ihave numbers in every cell now in every row for example Ag2:An2 i want to find the values less than 50 then i want to write thier month's names in the cells from Ap2:Aw2 i want to do this with every row from row 2 to 55

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 18, 2014

I have 4 grids on the trending tab. I want to find the top 10 highest Color assets from Fleet Volume -Color tab under the correct month.

So for example, if the month is May as in cell G3 on the trending tab, then look at the data under the month of May on the Fleet Volume-Color tab and find the top 10 assets and drop in the City, Address, Model, Serial Number and then volume to the 1st grid on the trending tab, then repeat for Highest B&W

I want to the do the same for each of the other 3 grids on this tab. I want this to update based on the month on both tabs.

Of course, the data on the Fleet Volume tabs is a small range due to size, the data is a lot larger.

Is there are way to do this with a formula? I tried Large and small formula but not too sure how to bring over the other data like City, Model etc.

View 4 Replies
View Related
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))

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

View 2 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
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.

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

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

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

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

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

View 1 Replies
View Related
Jun 9, 2014

Here's my table:

2014 2014 2014 2013 2013 2013

Jan Feb Mar Jan Feb Mar

A 56 63 100 78 25 44

B 48 12 74 32 65 97

C 45 85 96 42 82 73

How will I use index match function to get the data that correspond to each criteria? For example I am looking for the value of A in Mar 2013.

View 4 Replies
View Related
Jan 8, 2010

See my attached spreadsheet. I am trying to get start dates and end dates based upon rates. The first start date is really simple, I just use A2. In F2 should return the last date from A2:A27 where the rate is I2. In E3 The I would like to return the first date in A2:A27 where the rate is G3. and so on. If the rate is 0 then the date can be blank or 1/1/1900.

View 2 Replies
View Related
Dec 22, 2011

i have over 1000 line invoice # with multiple listings....however they have different dollar values for each item

702-0195660 = $10

702-0195660 = $12

702-0195660 = $15

i have an index match in my "missing worksheet" **see attached**

but that is looking up only the first value

702-0195660 = $10

702-0195660 = $10 (should be $12)

702-0195660 = $10 (should be $15)

etc

i have attached the sheet..ive been on the past day!

attached file link below [URL]...

View 9 Replies
View Related
Dec 23, 2011

I'm running a College Football Bowl Game pool. NO money just fun! I've just about got this down except that my Standings area for this years members is not working like I want. There are 24 members and I want them ranked 1-24. This works however, when 2 or more members have the same score....only the first name in the column with that score appears.

Vea, Ray WA955

Braham, LukeHI916

Braham, LukeHI917

Bowers, LarryGA908

This cell comes back with 3 different dupes:

=INDEX(H$1:CB$1,1,MATCH(D87,H$83:CB$83,0)-1)

H1:CB1 are the Names

H83:CB83 are the points for each name

D87 comes this cell : =LARGE(H$83:CB$83,E87)

How can I tell Excel to show all 24 names including the dupes?

View 1 Replies
View Related
Jan 30, 2014

I am trying to retrieve values with INDEX and MATCH. I have column B that contains "i", and "m".

I have formulas for the first occurence of "m"

=(INDEX(NUP!$J:$J,MATCH("m",NUP!$B:$B,0)))

Which will look for the first "m" and output the value in column J that aligns with the "m"

How would I alter this or a new formula for the nth occurrence of "m"?

View 5 Replies
View Related
May 30, 2007

I'm trying to do in Excel 2000 that seemed way easier before I tried to write the formula. Any thoughts would be much appreciated.

I have some data setup more or less like this:

1State DeptJan Feb Mar

2California 50128 31 6

3Utah 20119 3 11

4California 20122 49 22

5New York 301 29 51 15

I'm looking to write a formula to retrieve the numerical value located in the cell for a particular month, across from a particular department number and state - so I can plug it into another spreadsheet.

I tried the following formula for January, but I get an "N/A" error when I hit 'Ctrl+Shift+Enter' to use it...

=INDEX(C2:C5,MATCH(1,("Utah"=A2:A5)*("201"=B2:B5),0))

Can you not use static values in this type of formula?

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

View 9 Replies
View Related
Mar 21, 2014

I have created a sales pipeline list in a workbook. One of the sheets ("Contacted potentials") has the following setup:

A---------------------B---------------C----------------D

Company name--------Location--------Probability--------Total company sales

Rows 1, 2 and 3 have other information. The actual list starts at the 4th row.

In another worksheet ("Overview"), I want to create a list of companies that have agreed on working with us (displayed by a probability of 100%). This should preferably be done in column A. Furthermore, column B should list the total company sales for that specific company. I have tried this myself using INDEX and MATCH formulas, by using the following formula:

=INDEX('Contacted potentials'!$A$4:$A$1000,MATCH(100%,'Contacted potentials'!$C$4:$C$1000,0))

But this only displays one of the companies, not all those which have a probability of 100%.

Ps. it is possible to automatically update this list (by pressing F9) when a company's status changes to 100%?

View 5 Replies
View Related
Dec 15, 2009

In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?

View 4 Replies
View Related
Mar 1, 2014

making a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results

View 6 Replies
View Related
Aug 26, 2009

I have a list of data that I want to average the number in column B when there is a match in column A

COLUMN A | COLUMN B

alpha | 100

beta | 200

gamma | 300

alpha | 400

beta | 500

What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.

View 4 Replies
View Related
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
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
Aug 19, 2008

I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed

View 9 Replies
View Related