Multiple Index Match Formulas Instead Of Static If Vlookup?

Apr 10, 2014

I attached a file containing two sheets. The first sheet is called Input on this sheet there are several categories of which I displayed the first three on. My ultimate goal is to get the Input per category broken down horizontally over the line-items on the Output sheet. Every category contains several line items to which costs are attached (f.e. in category 10, line item 4000, the costs are 33,000$ and so on).

The problem is that every month the costs are changing per line-item, but also the number of line items per category will change due to the fact that the line-items are only displayed if there are in a specific month costs booked to the line-item. In other words sometimes line-items are added due to costs booked at the line-item, but if in one month there are no costs booked at the line-item it disappears from the overview. A formula containing: '=if(category=10,vlookup(... is to static due to the changing line-items.

View 8 Replies


ADVERTISEMENT

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

INDEX / MATCH Using Multiple Static Lookup Values From Dropdown

May 20, 2014

I am trying to do an INDEX/MATCH to return a value based on multiple lookup_values.

The workbook is attached, and what I am trying to accomplish is this:

If 50, 75, or 90 are selected from Column D drop down of this (Main) worksheet, then return Column A, B, and C values for those rows to the Report worksheet.

So in my sample spreadsheet, John, Jim, and Jack's information should be returned on the Report worksheet because their Column D shows 50, 75, and 90. Alice, Wanda, and Cindy's information should not be returned on the Report worksheet because their column D shows Budget Only, Contacting, and 100.

My current INDEX/MATCH formula is =INDEX(Main!$C2,MATCH(50,Main!$D2),0), but this only works when column D has 50 chosen because I have only asked MATCH to return info if it finds something with 50 because I do not know how to ask it to search for multiple lookup_values.

So, as you will see, only John's information is returned on the report worksheet, but Jim and Jack's are not.

View 7 Replies View Related

Index With Multiple Match Formulas Returning Undesired Value?

Nov 20, 2012

I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.

I need a formula that will find the column that meets two conditions:

1) Today's date
2) A specific time (referenced from a cell on the main sheet)

It must then pull the data from the cell in that column that is in the row marked "Total".

Here is an example of the data set from the web query:

Excel 2010BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATA
UAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT3
Total19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-
Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-
Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-
Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-
Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-

[Code]......

I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:

=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0)*AND(MATCH(TODAY(),'Rodeo Data'!$D$3:$JS$3,1)))

This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.

=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(TODAY()&O3,'Rodeo Data'!$D$3:$JS$3&'Rodeo Data'!$D$4:$JS$4,))

This one returns the standard "#N/A" error message.

I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.

The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.

It may also be worth noting that the number of columns and rows in the data set is dynamic.

View 3 Replies View Related

Index / Match Or VLookup For Multiple Criteria

Jun 9, 2014

Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:

EnglishSS1 EnglishSS2 EnglishSS3 Rating
10 20 30 3
20 30 40 2
30 40 50 1

Where my variables would be based on whether a person is under EnglishSS1 and getting a score of <=10, the resulting rating would be 3. I know this is doable by doing a vlookup with a range lookup value of false. However I have roughly 70 column headers and it will be a bit taxing to combine an If and Vlookup statement to address it.

I have attached a sample sheet for reference : Book1.xlsx‎

View 2 Replies View Related

VLookup Or Index / Match Multiple Occurrences?

Jan 24, 2014

I have some data which I update every now and again. Then I extract and clean up and currently have it output to 4 columns.

The four columns currently looks like this:

A | B | C | D
1 name name2 stats stats2
2 ....
3 ....

Each column is then populated with a decent amount of names and stats, and the same name occurs multiple times. I'm looking up a specific name in the A column, and can extract the stats in C fine using INDEX/MATCH, but it only returns the data from the first instance the name occurs. I've googled it a bit and believe I could possibly wrap my head around returning multiple instances.

However, there are a couple of other criteria for my extraction of the stats which makes me believe I'm in way over my head. The data is in chronological order, with the newest on the bottom. I'm wanting to extract the stats in inversed order so starting from bottom going upwards, if that makes sense. Further, preferably I'd want to limit the amount of extraction to X amounts. And lastly to make it really complicated, for the newest entries stats are not yet available so only the names (column A and B) are populated, I need it to skip those entries, since, obviously, there are no stats there to extract, and I wouldnt want to end up with a lot of blank cells in my summary.

View 2 Replies View Related

Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 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

Index And Match Formulas

Feb 16, 2010

Attached is a spreadsheet I am working on. The cells I am having difficulty with are I5 and I6. Cell I5 should be pulling a value from the 2nd tab based on C4 and C5 AND C6. Or at least that is my intent. However when I change C6 to 6 I get an N/A. When it is 5 based on 15,000 it enters $2,353.73 but according to my table it should be $2,710.23 I am having the same issue with I6. Based on 15,000 it should enter $190. Where did I go wrong.

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

How To Create VLOOKUP And INDEX Formulas

Jul 8, 2014

I would like to create a spreadsheet with 3 columns: employee ID, last name, first name. Then on the 4th column I would like to enter the employee ID and have the last name and first name auto populate into the 5th and 6th columns. How do I do this?

View 14 Replies View Related

Sorting Of Results Using Index / Match / Large And Other Formulas?

Jul 16, 2012

how to return the top 5 results in a set of data.

In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.

View 1 Replies View Related

Vlookup OR Index And Match.

Oct 22, 2009

I have a worksheet that has numerical data in A1:A22. In E1:E61 I have set values in numerical order and in F1:F61 I have a letter or letters that correspond to E1:61.

I am trying to write a formula in B1 that will look at the value in A1 and then match it up in E1:E61 and then return the corresponding letter in F1:F61.

e.g: in A1 i have an INDEX and MATCH formula

View 2 Replies View Related

Match/index/vlookup

Apr 3, 2008

I have two worksheets that I am hoping to use to generate one report in excel. Sheet1 contains the following information for my entire work center:

Benefitor Account Dollar Value Document #


Sheet2 contains a list of Benefitors that are relevant to only me.

I want to pull the information from Sheet1 where the benefitors on Sheet1 match the benefitors listed on Sheet2 into Sheet3.

View 11 Replies View Related

Index Vlookup Match

Dec 4, 2008

I have a tab that has Employee Name in one column and the benefit they chose in another column. The employee name is repeated a number of times since they have chosen more than one benefit.

I am trying to create a new tab where the employee name in is the first column (only once) and each benefit is listed in separate columns along the top. I want to put an "x" in the column for the benefit the employee has chosen.

Is there a way to say "look for this employee, see if this benefit is what they chose, and if it is, put an "x", if not, leave blank"?

View 9 Replies View Related

Vlookup Or Index/match

Jun 5, 2009

I am trying to get the info shown on table 1 into table 2. I suspect index and match is the answer, but I am not sure.

Table 1 Employee # Deduction Amount 101 AD&D 7.00 101 LIFE 4.00 101 LTD 12.00 101 STD 6.00 101 HEALTH 300.00 101 DENTAL 25.00 105 AD&D 8.00 105 LIFE 6.00 105 LTD 6.00 105 STD 300.00 105 HEALTH 25.00 105 DENTAL 8.00 112 AD&D 7.00 112 LIFE 5.00 112 LTD 13.00 112 STD 5.00 112 HEALTH 450.00 112 DENTAL 37.00 Table 2 AD&D LIFE LTD STD HEALTH DENTAL 101 105 112

View 9 Replies View Related

VLOOKUP / INDEX And MATCH For Second Value In Column?

May 1, 2014

If I have a column of data with a bunch of values (which can't be sorted & which is constantly changing so cant be broken into another column and then sorted) what formula do I need to output the second occurrence of a value in that column?

View 3 Replies View Related

Vlookup With Match / Index Option?

Mar 3, 2014

Attached example file has data with which I need to do the following;

On the tab "part list" I put in the part number in column F. Column C should then be populated automatically by looking up column F in the tab "Lookup" and showing the value found in column B of that tab.

The difficulty with this is, is that when the part number in column F starts with "DN", the lookup value should end with "FD/A" and when the part number in column F starts with "Z", the lookup value should end with "TY/A".

This last part I can't get it to work. I've tried vlookup with a index/match function inside, but it won't work somehow...

View 3 Replies View Related

Using Result If Index &amp; Match In Vlookup

Dec 25, 2007

I have used in Index & Match formula in column G as follows :

=IF(ISNA(INDEX(R:R,MATCH(E13,S:S,0))),"",(INDEX(R:R,MATCH(E13,S:S,0))))

The codes in columns R & S are as follows:

******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutR1=RSTU1New CodesOld Codes  2    310100199999V  4101002NEW  51010032270A  61010061100  7101009NEW  81010101104  91010111101P  101010121101S  111010141102  121010151102L  131010181130  141010222203  151010231106I  Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The code in column E & G is as follows:

******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG62H62G63H63G64H64G65H65G66H66G67H67G68H68G69H69G70H70G71H71G72H72G73H73G74H74=EFGH621106B|  631100|101006 641101D|  651101K|  661101P|101011 671101S|101012 681102|101014 691105R|  701103|101037 711104|101010 721105|  731106|101031 741106I|101023 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I have used a lookup table to calculate the value in column H as follows, but cannot get it to work

=IF(ISERR(VLOOKUP($E62,$M$3:$O$850,3))=1," ",VLOOKUP($E62,$M$3:$O$850,3))

View 9 Replies View Related

Index/match/vlookup And ODBC SQL

May 7, 2009

I want to create a formula that will use a database table as a lookup for index/match and vlookup type functions.

I have my ODBC connection connecting to a mysql database and am successfully able to run sql queries through excel.

Is there a way to get index/match/vlookup formulas to use the database table as the lookup range?

View 9 Replies View Related

HLOOKUP VLOOKUP INDEX & MATCH Etc

Feb 14, 2010

I don't want VB code but a worksheet formula.

I have a table full of names and cars sold. The table tracks the person's progress. It is like a diary. It's a simplied version of the DB I am currently working with.

Is it possible to do the following things? I have basically 2 types of questions.

1)When was the last red car sold?

2)When was a car sold irrespective of color?

The answer to each question will be put in separate columns.
I have only included January but I will do this for the entire year of 2010 and also I will do this for all of the names in my database. I think there are about 300.

When was the last time ANDY sold a RED car?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN I

When was the last time ANDY sold a car irrespective of color?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN J

When was the last time BILL sold a RED car?
The answer should be 1/10/2010.
The answer will be put in ROW B COLUMN I

When was the last time BILL sold a car irrespective of color
The answer should be 1/11/2010
The answer will be put in ROW B COLUMN J

I have attached a thumbnail of a sample Database

View 9 Replies View Related

Index, VLOOKUP, MATCH Formula

Feb 28, 2010

I have the following arrangement
PVCu_Woodgrain_DoorF190210300#REF!

PVCu _woodgrain_Door is a drop down list that selects from a number of named ranges.

The Cell that shows (F1) and is called DOORSTYLE with 6 choices F1, F2 ... F6 (I cannot change these to numeric only values)

300 is the 2 previous cells added together. It is called DRSZ1 and will match a numeric value in the named array.

My formula cell with the issue has this formula;
=INDEX(INDIRECT(ARRAYDR1),MATCH(DOORSTYLE,INDEX(INDIRECT(ARRAYDR1),0,1)),MATCH(DRSZ1,INDEX(INDIRECT(ARRAYDR1),1,0)))

I know MATCH(DOORSTYLE,INDEX etc. should be a VLOOKUP to find F1 in the named array, but I just cannot get it right.

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

Combination Of AVERAGE With VLOOKUP And MATCH / INDEX?

Aug 14, 2014

What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.

(some examples from the file)
NR2 NR DATE
100707987121951
100702347121960
100707750121960
100707721121960
100702422121960

[code]....

So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.

I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)

View 3 Replies View Related

Vlookup, Index, Match, Offset, What Combination Should I Use?

Sep 22, 2009

I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large. In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code.

The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same. Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns H-AH. It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.

View 3 Replies View Related

Returning Second / Third Etc Values Using VLOOKUP / INDEX / MATCH

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

Index / Match And VLookup Didn't Work?

Apr 28, 2014

I have a question in regards to excel. I have a set of data arranged as follows:

Date System Desc
3/7/14 IPE Alphamo
3/7/14 IPE Beta
3/7/14 i3 Gammana
7/7/14 ews Pisqrt
7/7/14 IPE Jaque
8/7/14 saa Mistiqe

And I need to put them into a calendar with the following format:

3/7/14 7/7/14 8/7/14
IPE Alphamo Jaque
IPE Beta
I3 Gammana
ews Pisqrt
saa Mistiqe

I have tried index and match, vlookup and etc but it didn't work.

View 4 Replies View Related

Vlookup Or Index/match With Most Recent Record

Feb 7, 2007

I have a list of people and the dates they completed activities on. I would like to do a vlookup (or index/match) that returns the most recent event. For example, if I was after the most recent date that 'rogers' went 'fishing' it would be 03 Feb 07....

View 9 Replies View Related

INDEX / MATCH Multiple Ocurence Match Values

Jul 11, 2008

INDEX/MATCH multiple ocurence match values needed

View 9 Replies View Related

Count Number Of Cells > 0 While Using VLookup / Index-Match

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







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