Fastest Lookup Method: Use A Key To Lookup A Value (VLookup, Index/Match, DGet, And The Rest)
Mar 26, 2008
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
ADVERTISEMENT
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
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
Aug 4, 2014
I am setting up a spreadsheet, that will have multiple sheets for each branch, this will than be referencing back to another sheet for data. As i will be replicating the sheets I want to make the formulas as easy as possible so I dont have to change them in every sheet. I have attached an example, the data sheet will have a table for each month, the remaining sheets will be each branch, ideally I would like to have a formula that looks up the data sheet and returns a value for each month and then be able to replicate the formula across each sheet. As the formula is looking at 3 different areas (date, branch and mortgage balance) i have tried using idex and match, but are not having any success.
View 4 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 22, 2014
So I have values in two colums like so -
A
aluminium
steel
plastic
aluminium
[Code]...
I now have another worksheet wherein I want a cell to recall a Value from range "A", using the code in range "B". So for example I'd want want my cell to search for the code to the left of it, "6464", and bring up value "Aluminium". HOWEVER, and here is where I feel it gets tricky - I intend to drag that cell down across a column - but when the cell encounters "6464" again, instead of showing "Aluminium", I this time want it to show the next entry under "6464" - "steel".
I hope this is clear. I'd post the excel spreadsheet but it is very large. Basically I want to INDEX MATCH function to remember its already recalled a specific value for code "6464" (in this case "aluminium") and to recall the the OTHER value related to this code.
My excel has a range of Purchase orders, and a range of the materials ordered under that PO number. I'm making a sheet that drags out all the PO numbers from my worksheet (which will have repetitions of 4-5 at a time), and then using those POs to find each material ordered that corresponds to that number.
View 1 Replies
View Related
Oct 23, 2007
I'm trying to do is (in column L) find which column the figure in column K appears in the preceding columns, then look in row 9 and return the month when this was charged. I need some kind of reverse HLOOKUP-type function!
View 2 Replies
View Related
Sep 12, 2009
I have used index,match for a two item lookup. But what is the formula for three items?? I have attached a file for an example.
View 2 Replies
View Related
Nov 5, 2006
I am having a heck of a time coming up with the proper combination of functions to return data to my worksheet.
Attached, you will find a screen shot of the pages I am dealing with.
Here is what I am trying to do:
On the page named Update Master, I am trying to put formulae into the highlighted cells C2:C11. Each cell will contain a formula to MATCH the team name in UpdateMaster!A:A with a team name in the chart found on worksheet WEEK ONE. Then, I want to return the value found 8 cells down, and 1 cell to the right of that MATCH. Here is a specific example:
In cell Update Master!C2, I need a formula that will MATCH the value in Update Master!A2, , with a value on the WEEK ONE! worksheet. (In this case, 2EZ. After locating the match, I want to return the value found in the bottom right of that players weekly score box. (WEEK ONE!C9...in this example).
I can't make a direct reference to the data...because the value in Update Master!A:A will change after each week. The team names will be sorted in alphabetical order after being sorted by the value found in Update Master!AE:AE...(each player's accumulated score).
I have tried so many combinations of MATCH, LOOKUP, OFFSET, INDEX, ROW...etc., and I keep getting VALUE# or NA# errors at some point in the computation. Maybe one of you can point me in the right direction.
View 9 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
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
Nov 13, 2008
=INDEX($AS$9:$AT$33, MATCH(B4,$AS$9:$AS$33,), MATCH("PERCENTAGE",$AS$9:$AT$9,))
That's all fine but some of the items in the list will not be able to match the lookup table and in this case I want it to say 0 (zero), but instead its saying #N/A which I understand means data unavailable. Is there a way of saying if so and so doesn't match put 0 instead of #N/A?
View 4 Replies
View Related
Jul 12, 2014
I need a formula for left coulmn lookup (using index and matching function) and lookup array has duplicate value.
View 1 Replies
View Related
Jul 23, 2014
I'm trying to perform an index / match lookup that can have a dynamic indexed column.
Sheet1 has the master table and sheet2 is where I'm putting the formula. The table on sheet1 has a RecordID field that uniquely identifies each record and that must be typed into Sheet2 [RecordID] column and then matched to sheet one to identify the record
Code:
MATCH([RecordID],Table1[RecordID],0)
The column headers on Sheet1 are used as data validation for the column [Field] on sheet2. The value of the Field column on sheet two should determine the the column on sheet1 to index.
This is what I have so far:
Code:
=INDEX(MATCH([Field],Sheet1!A:AQ),MATCH([RecordID],Table1[RecordID],0))
View 7 Replies
View Related
Feb 21, 2007
=INDEX([ipathfall07shoes.xls]Sheet1!$B$2:$W$139,MATCH(C17,0),1)
View 9 Replies
View Related
Sep 3, 2009
I would imagine that the title of this thread made little sense, so let me try to explain:
Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.
So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.
View 9 Replies
View Related
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
Feb 14, 2014
I am building a timeclock, and am using a user form to allow people to sign in/out.
I have a punch in form that logs the inputed username and password (after being checked against my employee list), along with a count of their username on this sheet (as a count of how many times they have punched in) and a timestamp (column 4). These values are all pasted to the first blank row in my worksheet "PunchInTimes".
Then, I have a punch out button that logs these same values the same way, only on "PunchOutTimes". Then, the idea is to link the index number of the PunchOutTimes record and PunchInTimes record for any given user, so that when they punch out, a complete record is pasted (to the first blank row) in a separate worksheet called "Timesheets".
I have been able to paste the username and count to the "Timesheets" sheet, however as soon as I try to index/match the count and inputed username (on the punch out form), I get an error 13 type mismatch.
I'm trying to paste the corresponding punch in time to this punch out time, so ive used an index/match with two columns (username and count) found in "PunchInTimes", and try to return the corresponding 4th column value (punch in time). After pasting the "punch out only" values to the PunchOutTimes, i used a "With/End With" statement, and set wsc equal to my 'timesheets' worksheet at the beginning of my code. So the line where I get my error is below in bold:
[Code] ......
View 4 Replies
View Related
Jun 25, 2014
I'm struggling with match, index and lookup functions. I have a formula in column D that calculates the percentage of answers against the total score for that category. The way it calculates enables me to delete rows if they are not needed and still provide a total count and a percentage score.
Now the percentage is returned from a dynamic data set, I want to plot the percentages on a spider/radar chart. Instead of manually creating the chart or using a pivot, I wanted to lookup the percentages (column D) against the category (Column A) in question and plot so the chart updates as the user changes the answers.
View 3 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Jan 11, 2007
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
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
Apr 25, 2014
My aim is to return a value from a table (Price of models) using a two way lookup method.
E.g. The code is CA45-AI, I'm looking for a formula to match the first 2 letters 'CA' to the TYPE RANGE (cell range) so excel knows to return a value from the CA row.. then, match the number '45' (from the CA45-AI (cell b3), so in the end.. excel returns the value (price) '90'.
Another example, Cell B5 is RA34-AI, so excel should return a value of 80 from the Price Of Models (cell range).
View 6 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
Dec 21, 2012
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?
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 6, 2009
I am creating a spreadsheet for the mortgage department. I have 2 tabs, appraisal fees & construction inspection fees.
Appraisal Fees will be completed by the processor as the applications are received and fees are collected. Column E is to identify loans that are construction loans, marked with a 'Y'.
In the construction tab, I want to bring over columns C & D from appraisal fees when there is a Y in column E of appraisal fees tab. I want to index this so that it starts from the top of the construction inspection fees.
Is it as simple as adding an index function to the start of the if formula?
View 8 Replies
View Related
Sep 30, 2009
I have used this handy formula
=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A2))
to create an index page that looks like this right now:
Index
35035-9
37032-14
37065-6
37079-4
Now what I would like to do is get cell B1 on each of these sheets to post in the next column without hardcoding the name of the sheet into the formula. So in the cell next to 35035-9, I would like the formula to look at worksheet name, and then go to that worksheet, and return cell B1. Same for all the other worksheet names.
There will be multiple additions of worksheet to this list over the course of the next year and I want to be able to index them easily.
View 2 Replies
View Related
Jul 14, 2009
Can they be used to reference data based on data inserted into more than one cell? I have attached an example as its kind of confusing to explain.
View 14 Replies
View Related
Oct 19, 2007
I have a formula I need to modify.
In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c
The idea with the orginal formula was to look up the item and return the date
=MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0))
Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?
I trying to return the most recent date and price for a item.
I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.
View 6 Replies
View Related
