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?
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.
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.
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.
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!
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.
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?
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?
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.
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.
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.
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:
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.
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
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.
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).
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)
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?
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.
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?
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.
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.
