I have a list in which I have to lookup various Groups and find a Name accordingly. I am now using a very simple IF formula but I get blanks too. Refer below for an example. (A and B are data table, D and E are lookup).
Sheet1
ABCDE1NameGroup GroupName2 A A03MikeA AMike4 A A05JasonA AJason
Spreadsheet FormulasCellFormulaE2=IF(B2=D2,A2)E3=IF(B3=D3,A3)E4=IF(B4=D4,A4)E5=IF(B5=D5,A5)
Excel tables to the web >> Excel Jeanie HTML 4
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%?
Populate my drop down list. I have 2 columns. I want to populate my dropdown list with data from column a that has a specific value in column b. is this possible with out using VBA?
E.g.
Column A / Column B
123 / TEST
126 / TEST
100 / BACON
231 / CHEESE
basically I would like a drop down list that contains the constants from column b. so from the example above it should contain 123 and 126 as options.
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.
Let's say I have a list in worksheet 1. It's in column A, starts in row 1 and goes.
Red
Orange
Yellow
Green
Blue
Purple
In worksheet 2 I want to list in row 1, starting with column A, which colors have a value next to them. I want the list to match the first worksheet's order. I'm looking for a formula solution.
Example 1
WS 1
Red 3
Orange 4
Yellow 5
Green
Blue 1
Purple..........
I have a table of sales by month per account. Each account has an associated country, but there may be many duplicates of each country per month.
I want to create a formula which indexes the country field and gives me the country with the highest total sum for a specified date range. I'd like to be able to drag the formula down and have the country with the highest sales, then the second highest, and third highest, and so-on. Column ranges are specified below.
K - L - M - N
------------------------------------------------
Month
Year
CtryDsc
Rev
1
2011
[code].....
I was thinking of using an array function utilizing index, large, sumif, and match, but I am getting stuck. I'm honestly not sure if what I'm looking for is even possible, but in Excel, everything is possible, so there has to be a way!
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 RelatedI 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.
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 RelatedWe 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.
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?
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.
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].....
INDEX/MATCH multiple ocurence match values needed
View 9 Replies View RelatedIs 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 RelatedI 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
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 RelatedI 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)),"")))}
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))
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))
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 RelatedI have never used the aforementioned functions before, but i assume thats what i need to do here....
In the attached table, what formula do i use to return the value '8' i.e. whats in column "Epsilon" and row "Three"
If you could let me have the generic formula i need as well, that would be good.
I can't figure out why the N/As are coming. In G2 I have =INDEX(Area_Codes!$D:$D,MATCH(F2,INDEX(Area_Codes!$B:$B,0),0)). If I replace F2 with 407, I get FL which is right. But why do I get N/A??
View 4 Replies View RelatedL26 returns incorrect information. Input parameters are F29 and F30. In its current state, it returns the value “b”, where I think it should be returning “1”, i.e the intersection point for “3Ph” & “Single-core 70°C thermoplastic non-arm Cu Table 4D1”
View 6 Replies View RelatedIf the value on sheet2 columnA ,matches value sheet1, column AC
then corresponding row value sheet2,column E be put in corresponding row for matched value ,sheet1,column Z.
I have the following array formula that matches against a table with 3 criteria (one specfically is a time), it works ok until the time criteria is not of the value searched for, it may be +/- 30 minutes out, is it possible for the formula to index/match to the actual value or to find the nearest match possible, its in column B.
View 3 Replies View Relatedinsert =IF(ISNA into the following formula? =INDEX(Data!$AE$2:$AE$10,MATCH(1,(Data!$AI$2:$AI$10=$A$11)*(Data!$AD$2:$AD$10=Sheet1!$A$6),0))
View 3 Replies View RelatedI have the following code from my UserForm that works fine for my Summary sheet.
Private Sub TextBox2_AfterUpdate()
Application.ScreenUpdating = False
'//Get the Truck # to act on
Dim varInput As Variant
varInput = TextBox2.Value
'//Exit if no input
If varInput = "" Then
Exit Sub
End If
What I am trying to figure out is to do a Index,Match or what ever it takes to populate the Truck sheet (250+) with info from the user form.
TextBox1 is the date. TextBox2 is the truck number, 3 is the Milage, and 4 is the Cost.
All in relation to the following sheet for the truck: ...
I have the below formula which works fine when I only want it to lookup one sheet but I am now trying to get it to lookup 2 sheets.
I have tried numerous variations but still no luck. Plus I am not sure that with the formula that I am using it is even possible.
HTML =INDEX('2008'!$B$2:$J$1697;MATCH($B6;'2008'!$B$2:$B$1697;0);2)+INDEX('2007'!$B$2:$J$1697;MATCH($B6;'2007'!$B$2:$B$1697;0);2)
I am having trouble finding the right formula to use to get the data I want. Let me outline it below:
Worksheet 1 contains raw data:
Column B : Date/Time i.e 8/1/2006 9:31am in one row, 8/1/2006 9:32am in the next row etc..
Column C: Temp i.e 33.5
Column D: Hum
etc...
Worksheet 2 is a summary worksheet and contains:
Column A : Date i.e 8/1/2006
Column B: Time i.e 9:31am
Column C: Max temp i.e 35.8 (calculated via array formula)
I wish to use a formula (which I can copy down the worksheet so it needs to apply to the whole column of the reference worksheet) to find the time at which the max temp occurred on each particular day. I don't want to use individual references for each day as it will take too long!