# Excel 2007 :: Convert VLookup To Match And Index Formula

Sep 21, 2012
HTML Code: VLOOKUP(M2,'FX Rates'!$A$1:$P$199,2,FALSE)

I have lots of vlookups in my spreadsheet. Match and Index is a better method and should speed up my spreadsheet (1000s of vlookups!).

View 5 Replies
ADVERTISEMENT
May 22, 2014

I have two data sets across two worksheets. The first worksheet contains sales order numbers (Sheet1:column A) and other data . The second worksheet contains sales order numbers (Sheet2:column A) and the product details.

On sheet1, I had to manually duplicate a sales order number (inserting another row) if the number of units of the order is greater than 1. I then need to fetch the product details from sheet2 for each unit for that specific order number. However in sheet2, there are multiple products for one sales order number.

How do i create a lookup/match to fetch the product details for each sales order number without duplicating the product details if there are multiple products for one sales order number?

I know a simple vlookup function will return the values that it matches first and that is not what i want.

Example: Sheet1

Column A (Sales Order#) | Column B (Quantity)

0417436GPCP | 1

0417436GPCP | 1

0413412FACY | 1

0413412FACY | 1

[code].....

View 6 Replies
View Related
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
Jul 30, 2014

I have a workbook that has 5 worksheets. Worksheet1 is the Summary tab, Worksheets 2-5 contains data all sitting under the same headings and formats however vary in row number e.g.

Worksheet 2 - 63000 rows

Worksheet 3 - 48000 rows

Worksheet 4 - 23000 rows

Worksheet 5 - 21000 rows

In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.

=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE,

COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI

[code]....

View 4 Replies
View Related
Feb 28, 2013

excel 2007. Here is the situation:

I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:

A

B

C

D

[Code].....

So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.

The formula I have at the moment is this:

{=INDEX('Asia'!$1:$1048576;MATCH(B3&C3;'Asia'!D:D&'Asia'!E:E;0);9)}

How do I incorporate the logical test for it to look for the proper worksheet knowing that all my continent worksheet have the same structure?

View 3 Replies
View Related
Mar 26, 2013

currently using Excel 2007 with Windows Vista.

I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).

Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))

which I found under: Find row, find value, then return column heading

However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.

The worksheet layout is as follows:

Date

A

B

C

D

Date

Rank

Header

[code]....

The return value under Header should be C.

I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.

View 2 Replies
View Related
May 23, 2014

I have one worksheet. I am needing to match up column G cells (TELCEL/MULTI) with value on E3 and with value E4 and give consecutive numbers on column F depending if the matched column G with value on E3/E4 is on same date of column L and if column Q (Tipo de movimiento) appers "Ventas"; I have plenty blankcells and other values on column G.

column E

# DE EVENTO

COMPANIA

INICIO

MES

Clave del producto

Descripcion

Fecha del movimiento

Clave de la tienda

Clave de la caja

Clave del usuario

Clave del vendedor

Tipo de movimiento

row3

TELCEL

[Code] ........

I have to put 1 to first and then sum one if condition is true

1

TELCEL

TAT

may

TAT31

Telcel, Telcel tiempo aire $31

01/05/2014

01

01

3

Ventas

View 4 Replies
View Related
Mar 15, 2013

I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.

Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.

View 4 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
Dec 28, 2009

I have the following formula in a cell:

=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9

When the workbook with this formula is open it slows down other workbooks quite a bit.

If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.

How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.

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
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
Oct 4, 2013

I have two worksheets, contractor & list. Assume that Column (A) on the "contractor" worksheet is a named range from Column (A) on the "list" worksheet. On the "contractor" worksheet I would like to put in the contractors name, and auto populate the pay value in column (B). I have been using a Vlookup formula, but need to automate this process a bit more.

"Contractor" worksheet - Two columns: (A) I will input the contractors name from a dropdown list based on name range from my "list" worksheet. (B) is where I would like to populate the pay base on column (B) in my "list" worksheet.

Contractor (A)

Pay (B)

Jill

Fred

Jack

View 1 Replies
View Related
Dec 9, 2013

I am using Excel 2007, I have a customer sheet and a sales sheet in the same work book. My customer sheet lists all customers with a single entry only each with a unique customer code. My Sales sheet lists at an item level but does include the customer code so if my customer has purchased more than one item the customer code can be repeated on the Sales sheet on multiple rows.

When I do a vlookup to display the amount each customer has spent on the customer sheet, only the very first sales figure is displayed, if further down the sales sheet the same customer has made another purchase I want this figure to be added to the first figure and all I want to see on my customer sheet is the final total figure.

The vlookup formula I am currently using is:

=VLOOKUP(G2,Sales!$A$2:$B$1785,2,FALSE),

I'm thinking maybe a Vlookup will not do what I require, maybe I need a different formula/function?

View 8 Replies
View Related
Mar 19, 2013

Code:

=D5-VLOOKUP($C5,$C$45:$F$80,2,0)

I have formulas such as above in my Worksheet. So the above is in Cell H5

For the Range $C$45:$F$80, I am inserting rows (So moving down the data) and copying data from $C$5:$F$40 as values into the new space in $C$45:$F$80

I am doing the above using a macro, but when I run it I want this

Code:

=D5-VLOOKUP($C5,$C$45:$F$80,2,0)

to stay static, but instead I end up with

[CODE=D5-VLOOKUP($C5,$C$85:$F$120,2,0)[/CODE]

So my Table Array $C$45:$F$80 changes to $C$85:$F$120

How can I keep it as $C$45:$F$80

The other references in the formula seem to stay as I want them.

View 2 Replies
View Related
Dec 19, 2011

I am trying to find a formula for a vlookup that will check one cell for any value but if that cell has no entry then it will check a different cell to return the value how do i do this?

TEAMCSS Wetherill ParkCT NAME

above is where i need it to look CSS wetherill Park is cell C1 the cell that has no entry is H1, both of these cells have validation lists in them,

OS windows XP excel 2007

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