I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears 9999 2004/5 5 4 9999 2005/6 7 8 9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
ID 9999 FY Apples Pears 2004/5 5 4 2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.
I am trying to create an automated receipt for a list of artists. I have given the artist an individual reference so I am doing a VLOOKUP to return description/price value etc but I can only get it to return the initial value and I have approx 10-15 works under each reference. Maybe VLOOKUP is the wrong formula to use.. I have attached the file.
I have to write some VLOOKUP formulas to pull several items from a report, the problem is there could be multiple items for each lookup value as the example below:
Company Data ABC Monday ABC Friday ABC Thursday DEF Friday GHI Monday I need to reference every piece of data for every company but using the VLOOKUP formula, I am obviously only able to get the first instance of the lookup value (company ABC)
How to I write the formula to lookup the other data? The practical appliation I am using this for has anywhere from 300 to 400 lookup values.
I have been trying to auto update a table by looking up information from a table on top. However, for the top table there are multiple entries in the same column, and my look up only checks the first row and ignores the rest.
My formula at this point is: =IF(ISERROR(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))=TRUE, "", IF(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4)=0, "", INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))).
If you can take a look at my attachment, this formula is in the cells I40:W40. Eventually, I will want to populate the entire bottom table with the same formula.
I have a worksheet which Sorted in ColA contains Product Id#, ColB contains Vendor, ColC contains Grade# (1 thru 6). Now there may be multiple Vendors (ColB) for same Product # (ColA) but with different Grade# ColC).
I would hope if you can please help me formulate for ColD to pull (list) ALL Vendors for the same Product# (there maybe as many as 6) PROVIDING that the Grade# (ColC) is either 3 or 4.
I'm working on a database right now for our school, similar to a directory. What I want to do is be able to look up what items a staff member has been issued and when/if they were returned. What I have right now is one sheet with all the information about the staff that looks like the one below. I want to create a separate sheet where you could look up a staff member by last name and can find all the information in the array relative the the specific cell looked up.
Last Name First Name Position Building Items
So, on the "lookup" sheet, I want to be able to input either Doe and have the look up return cells B2:E6, or if I input Smith it should return cells B7:D11.
I've been experimenting with Vlookup, hlookup, index, and match but haven't been able to get exactly what I want..
Just learned a little about vlookup and wondering if it can be use to solve the following. I have attached "worksheet1" that shows what I'm trying to do. The "detail" sheet is downloaded from another software package. There may be one to five rows of Product #'s with their component # and percentage on the same row. I would like (on a new sheet) put one Product # and all the corresponding component #'s and percentage's on the same row. (An example of how I would like it to be is on the "one row" sheet.)
i am trying to create an interactive calendar that displays what is happening on different dates depending on the activity. i.e. if 'workshops' is selected then it will show all the workshops happening in the month. the problem is that i have created a new line for each workshop in my table array and want it to display all the returns in a single cell on the calendar. I am using a vlookup and have used a unique reference so that it picks up multiple rows, but it will still only return one value.
is this possible or do i need to collate all the data into one cell? I have attached the spreadsheet as i am sure the above prob does not make sense.
At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1 Row 1: Mike │ "Great to work with" Row 2: Mike │ "Bad manager" Row 3: Tom │ "Great guy" Row 4: Mike │ (blank) Row 5: John │ "Cool" Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
="Comments for "&[ref to cell containing "Mike" in another sheet] - Great to work with - Bad manager - Best boss
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 a table with three columns. I'm building a calendar on a separate worksheet and am looking up the "value" based on a calendar date. So if a date falls within any of the ranges, I'd like to return the value in column C. For example, if the date is 02/07/12, I'd like for the result to be value 1, or if the date is 04/17/12, then I would like the result to be value 3. I've used a nested vlookup, but all that give me is the value when either the start or end dates match, but I can't get a value when the date falls within the range. If the dates were consecutive, I would simply use vlookup/TRUE, but the dates are not consecutive.
Australia SOME VLOOKUP FORMULA SOME VLOOKUP FORMULA
I then have another tab which is similiar to this:
Australia Danny 23 Australia David 25 Canada Ben 35
Basically what I want is....where ive put some formula I want to lookup the title, in this case australia which is the cell above, search within the table then enter the name to the right of that. I have done this, however it only ever uses the first name on the list..... Example i always get danny on a role, i have managed then to get it to say david underneath but then if i copy the forumla down again it continues with danny, david etc.
Is there a way to perform a vlookup with two lookup values?
I'd like to look up the value of A1 and B1 in a table with A1 and B1 values included to return cell C2 from the table. I've had limited succcess using concatenate and then using vlookup on that cell but i'd like to not have to do the concatenate step.
Is there a way that I can do a vlookup in 1 file and specify more than 1 data range to lookup the data in?
In this case I have one file to put the vlookup in and a second file with more than 1 tab and I want to have it search each of those tabs and return the result. The format of these sepatate tabs are to each other and for that matter, identicle to the main file.
=VLOOKUP(C29,'Zero Days YTD'!B:Q,16,FALSE) is my current formula where C29 = Jack Bates. As of this month Jack Bates is now in two places on worksheet Zero Days YTD. One is named "Jack Bates (from AMU 3/1/07)" and the other named "Jack Bates (to APU 3/1/07)"
I tried =VLOOKUP("Jack Bates (from AMU 3/1/07)"&"Jack Bates (to APU 3/1/07)",'Zero Days YTD'!B:Q,16,FALSE), but that didn't work.