Advanced Indexing/VLOOKUP
Jun 3, 2009
I have two worksheets that are formatted differently. One has a list of employees and the other has the same list of employees but with associated employee IDs. I need to pull the employee IDs to the other sheet.
Let's call the two worksheets A and B. On Worksheet A (the one I'm working with), here's the format (column A):
Employee Name
Doe, Jane
Doe, John
Roberts, George
In worksheet B, here's the format (column A | B | C):
Last Name | First Name | Employee ID
Doe | Jane | 1105
Doe | John | 1106
Roberts | George | 1107................
View 9 Replies
ADVERTISEMENT
Nov 11, 2009
I am trying to index and match information and trying to show the multiple values that go with it. Then I am trying to do the same with vlookup.
View 4 Replies
View Related
Jan 20, 2010
I wish to perform a VLOOKUP on my spreadsheet. I have my table to be called upon all setup and can get the VLOOKUP to work, but I need some help finishing it.
I want the VLOOKUP to take the information from A1 AND C1. Can I do this in a VLOOKUP?
So instead of the VLOOKUP just taking the Lookup_value from just A1 or C1, I want it to match BOTH before it shows me the result.
View 9 Replies
View Related
Oct 14, 2008
I have a spreadsheet that currently includes the following formula:
=VLOOKUP($C42,$A$42:$B$61,2,FALSE)
Col A is numeric output
Col B is staff names
Col C specifies top ten output numbers from Col A
The above formula goes in column D and It returns the staff names with the top ten output. My problem is that if two members of staff have the same output it formula will repeat the name only one staff member in both rows. Is there any way of altering the above formula so that it will show both staff names in each row?
View 3 Replies
View Related
Sep 22, 2009
find a row containing text. Like when opening a PDF file with Excel, all the pdf code is in column A. I would like to create a formula that would search column A for the text "Example" and have the row number be the result.
I've tried using index, match, find, search, etc.. and nothing seems to work.
View 5 Replies
View Related
Oct 22, 2007
I have to find the maximum value of a column (J16:J115) and refer to the name in column A corresponding with the number found... everything works perfectly with :
=INDEX(A16:A115;MATCH(LARGE(J16:J115;1);J16:J115;0))
But when there are two maximum values it only gives me the first one... is there a way I can get both (or more) names in different cells ?
View 9 Replies
View Related
Jan 6, 2010
In the attached file the
„«All the data is available in the details sheet
„«Input field is B20.
„«There are 6 Rate Schemes. All the input fields are present in all
„«Again there are 3 Subline of Business
Need .. when I put a input field and select a C15 and C17 the Exposure Curve, Flexa, Other should get displayed.
Hope I made the scenario understandable.
View 2 Replies
View Related
Dec 9, 2008
I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.
indexing a range from another spreadsheet, but when I tried to use it again on a different range & copied & pasted and changed the ranges, I get #REF!, which I know means I screwed something up, I'm just not sure what & I don't know what the 1's & 0's mean.
=INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))
View 7 Replies
View Related
Aug 22, 2006
In the attached sheet, I'm trying to get excel to return the top 5 values in an array, based on the preceding cell. This example is for a Football League, in which the QBs, RBs and WRs all have different salaries. The objective is to return the salaries of the top 5 paid people in each. Don't need to return the name, just the salary amount, so that an average can then be taken to figure future salaries.
View 5 Replies
View Related
Feb 23, 2014
I am trying to create a form where my staff can use a drop down box to select their role on the drop down bar on the "Template" tab- M12 (highlighted orange) that can reference to same role on "sheet 2" and show the corresponding data in column C, highlighted in green. The output of the formula starts on "Template!23" - highlighted in yellow. I have started to use an array formula but cannot fully utilise it as I need the row reference needs to be variable. Is it possible to use multiple "ifs" to account for the changing inputs on the drop down bar? What do you think? <attached>
View 5 Replies
View Related
Nov 2, 2013
indexing complete data sets for each entered value. see example to better understand what I am trying to do. I have been trying for weeks.
Steve
INDEX.xlsx
View 14 Replies
View Related
Dec 10, 2009
I'm trying to improve a code that Indexes a series of prices to a day (the first day of a range). I'm doing this to numerous named ranges. The code that i build takes ages to do this:
View 9 Replies
View Related
Jul 2, 2014
I'm trying to get a document together that will spit out an index of all the files in a folder/subfolder with various attributes like title name/ hyperlink to the file/ type/ etc. How to add a function that will create a column/pull data for the author of the files I am indexing.
Code:
Public fPath As String
Public IsSubFolder As Boolean
Public iRow As Long
Public FSO As Scripting.FileSystemObject
Public SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Public FileItem As Scripting.File
Public IsFileTypeExists As Boolean
[Code] ...........
How to get a column that lists the author of the document.
View 2 Replies
View Related
Aug 29, 2008
I have a folder with about a thousand files in it, each one holds one worksheet.
What i need is a new file which lists all these file names in column A and, in column b, the value from cell A1 in each file against the file name, i.e.
book1 Apple
book2 Banana
I can list the files in the folder using dos, but cant rip the a1 cell values out,
so I need to open each workbook in turn, record the file name, and then record the value in cell A1 against it.
View 9 Replies
View Related
Nov 24, 2008
I would like to create and Index/List of all the Hyperlinks in a workbood and the locations of those links. It can be a little as just the tab/worksheet the hyperlink is located. I can then import those links into a spider to check to see if they are broken or not.
The following code is close, but I need it to loop through the workbook and list the locations as well.
Sub test()
For Each h In ActiveSheet.Hyperlinks
With ActiveCell
.Value = h.Address
.Offset(1).Activate
End With
Next h
End Sub
View 9 Replies
View Related
Feb 6, 2014
I copied and pasted text (last names) from Website A into File A. Then did the same from Website B into File B. I wrote index match formulas to compare columns between files. I've done this literally hundreds of times, and it's always worked. This time, however, I get ZERO matches - even though there are lots of them. Troubleshooting was straight-forward: it has to be website B. (Strange thing is, I HAVE used text from website B before in similar applications with no problems.) Something in the way they present the data is preventing matches. (Everything works when I manually type over text from Website B.) It's not an upper case/lower case problem. I tried copy-pasting values only - to no avail.
View 4 Replies
View Related
Oct 10, 2013
What is wrong in this Indexing code.
Code:
Range("R6").Select ActiveCell.FormulaR1C1 = "=IF($Q6="","",INDEX('Worker-Exempt'!B:B,MATCH($Q6,'Worker-Exempt'!A:A,0)))"
I get application 1004 error msg.
Cross Posting
HTML Code: [URL] ........
View 9 Replies
View Related
Mar 28, 2009
I was able to come up with a formula that returns multiple results when indexing rather than just the first match.
Now the problem I have is that my source table is going to vary in size. My source table in the formula I got working below is a dynamically named range 'ImportedData'. The data in this table will always start on Row 8 but the last row of the table will vary, so the bold/red areas of the formula need to be dynamic.
how I might modify this formula to allow for the dynamic table size?
=IF(ROWS(B$19:B19)
View 9 Replies
View Related
May 30, 2014
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
[Code] .....
View 2 Replies
View Related
Feb 12, 2010
I am indexing dates from one workbook to another.
There are some blanks in the list that I am using, and I need to the blanks to stay as blanks in the sheet that I am indexing to, however they are displayng as 00/01/1900.
View 4 Replies
View Related
Jul 2, 2014
I'm trying to get a document together that will spit out an index of all the files in a folder/subfolder with various attributes like title name/ hyperlink to the file/ type/ etc. How to add a function that will create a column/pull data for the author of the files I am indexing.
[Code] .....
Here's the website: [URL] .....
How to get a column that lists the author of the document.
View 3 Replies
View Related
Oct 22, 2009
Sheet2:
col A = contains the style#
col B = contains the color of the style
col C = contains the size of the style
col D = contains the qty of the style,color, size
Sheet1:
I would like to do the following:
A1 = input the style #
B1 = input the color of that style
C1 = input the size of that style
then D1 should automatically contain the qty of the mentioned style, color, and size.
View 4 Replies
View Related
Jun 17, 2009
I have an employee expense voucher with a column that calculates mileage. I also have a column that lists what the mileage rate is. I'd like for that column to adjust based on the date of the transaction/trip.
Here's an example. Trip between 7/1/08 - 12/31/08 = .585 mileage. Trip between 1/1/09 - 12/31/09 = .405 mileage. If someone turns in a receipt for a trip taken on 11/13/08, I want the form to adjust the mileage rate to .585. Wasn't sure if this is an indexing kind of thing, or if there's an if statement I could use.
View 3 Replies
View Related
Jan 27, 2010
hello honorable excel profesionals.
here is what i want the macro to do:
must start from the top of the colum and count cells with specific data in them:
if ''X'' or ''Xa'' or ''Xb'' -> start counting how many cells in a row have the value specified.
from here two things can happen:
if a cel with a value ''Y'' is met -> stop
if a cell with value '''Ya'' or '''Yb'' is met -> countinue counting untill Y is met
when I say stop I mean that the macro should imput, ona separete table:
1.total number of how many counts where made.
2.how many counts there where with a lenght of 1, how many with the lenght of 2 and so on
here is an example of data line and a table I want to have after macro is done.
Hope that this isn,t a problem to write otherwise il have to do this manualy - about 5000 rows of data...
View 10 Replies
View Related
Aug 3, 2007
I have a difficult one I think? I have a workbook in Sheet 2 I have a page with data.
I would like to create a Sheet 3 that would look at Sheet 2 and copy all rows (Range A to V) with the asterisk displayed in Column B... IF MORE THAN 30DAYS AFTER THE DATE ENTERED IN "Column C"....
View 9 Replies
View Related
Dec 1, 2008
Assume cell A1 is "ageioslop315555555138lkeameox". Is there a formula that I could use that could match 31555555138, and if it matches put the 31555555138 in cell A2. You can probably see where I am going with this, there is several thousand lines that has sporadic descriptions that I need to find out which ones have certain #'s that correspond with info. on our end.
View 9 Replies
View Related
Jun 10, 2009
with a complicated average function. I’ve made a tender comparison sheet which compares different suppliers and their prices. The suppliers are assigned a region (with data validation when they are added and I want to get an average on the specific article (on every row) on all suppliers in the same region.
All supplier names and regions are on row4.
See example below:
Art no: Supplier1 region1 Supplier2 region1
Price Price
A1 2 3
B1 1,5 2
C1
How do I get the average from only one region?
View 9 Replies
View Related
Jun 29, 2006
I am working on a large Audit project for one of my many bosses and I am a little stumped so I came to the experts. I have a list that is organized randomly and we need to have 95% of the cells deleted. I don't want to have to go through each time and calculate that out each time.
I am sure that I would have to find the last cell used in one of the columns and then multiply that by 95% and then delete from that row down in the sheet. (Since the sheet is already organized randomly it can delete the bottom 95% of rows.) I am not sure how to put it all together.
View 9 Replies
View Related
Apr 8, 2014
I have a large Excel with details of 1,000 staff
Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc
I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g
Frank, James
Wilkonson, Paul,
etc
In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR
Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?
View 11 Replies
View Related
Aug 5, 2014
I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
View 8 Replies
View Related
