I am currently having real problem using the formula below:
=INDEX(Data!C:C,MATCH(B7,Data!A:A))
What i am trying to do with this formula is return the cell address of an item that is in a different sheet.
The reference the for search is in Cell B7 which is a date, it then looks at a different sheet "Data" for this date which is in Column A. It then returns the Cell Reference of Column C of the same row.
Currently it is just returning the value of column C and not the cell reference.
I'm setting up an Excel (2000) sheet to record the results of a competition at my local photographic society. I'm trying to make it as simple as possible to fill out by the people using it, so have shaded the cells that require the user to put data in. I'll post a screenshot of the sheet, which is named "DPI", to make it easier:
(EDIT) screenshot removed - see attached file in post #3 below instead
I use one row for each member entering the competition. Each member can submit up to three photographs for judging. The names in B5:B30 are tied to the member number in A5:A30, and pulled in with VLOOKUP from a separate sheet containing all the club's members. The names in this example are, obviously, fakes. ;-)
The three blocks are to enter the image titles and associated scores. I have a range called "scores" which is defined as =DPI!$D$5:$D$30,DPI!$G$5:$G$30,DPI!$J$5:$J$30
The rank columns are simply calculated using the RANK function. E.g. the formula in E5 is =IF(ISBLANK(A5),"",RANK(D5,scores,0))
The cells at the top are calculated using the LARGE function upon the defined range "scores" (although they could equally use the SMALL function on the rank columsn instead).
The bit I'm stuck at is what I want it to say along the top. eg: "Leg 1 Winner: 30 is {insert image title} by {insert member name}"
Using the winner as an example, I want to take the winning score shown in D1, find it in the named array "scores", and return the image title to the left of it. Obviously I can't use VLOOKUP as the data is to the left of the lookup value as well as being in multiple columns. I wanted to use OFFSET to return the value of the column to the left, but to do that I need to pick up the reference of the cell containing "30" i.e. the winning score shown in cell D1.
How can I find this cell reference? Or is there a better way? I thought about the old MATCH/INDEX function, but INDEX doesn't seem to work very well with data in non-contiguous arrays as I'd have to specify which block to look in.
Once I can get the image title returned I hopefully sholdn't have any bother extending it to return the member name from a fixed column, but I can't figure out how to get the reference of the cells in the "scores" array that contain the winning scores shown in D1, D2 and D3.
i am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).
I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:
I have a sheet which uses a vlookup to find the data on a large sheet. normally to get the cell reference of the data i would use cell("address",......
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
Simplified Example: I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE) I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE) This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?
I am trying to create a lookup formula where the cell value to match has a formula behind it.
For example: =INDEX(AR7:AR371,MATCH(G28,AE7:AE371,0))
The trouble I'm having is that the value in G28 is derived from a formula and the Index Match formula then gives a #N/A result. If I change the value in G28 (a date) manually, so directly enter a date, the Index Match formula works ok.
Is there any way of getting the Index Match formula to work, or Vlookup would also do although that at the moment that has the same problem with G28 having a formula behind it.
I am trying to use vlookup so that when each site name is selected the relevant comments for that site are displayed.
I have a formula that is working, but it displays "0" if there is nothing in the cell. How do i get it to display a blank cell if thre is nothing in the reference cell.
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
A -> SNO B -> Customer Name C -> Product Name D -> MODEL E -> Quotation Rate I have to retrieve MODEL & Quotation Rate . and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1) In X1 sheet , I do have following columns A-> Product Name , b-> Model (as of now limited to 1 per product name) c -> Rate
Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .
I have problem with a spreadsheet that I am trying to create.
I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.
Now for planning purposes, I would like to see how much of each item I have on order.
I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:
=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.
For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is
A108: ="A"&MATCH(D11,'purchase order'!A:A,0).
Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B 1/1/2011 # 2/1/2011 # ... 12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
I am trying to use VB to vlookup between to workbooks 1. Make active workbook WBK1 2. Make workbook being open WBK2 3. Copy and Paste between WBK1 and WBK2 4. Have a vlookup in WBK1 and bring in the values from WBK2 5. Close WKB2 6. Copy, Paste, and transpose values in wkb1 within wkb1
The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.
I have a reference value(say 'X') at L3, now i want to select a value next higher to this value(ie..'X') in the column ''C'' say and also i have to print the corresponding values of the row corresponding to B,C,D,E,F etc.
I have some intersting issue. Actually there are lot of functions in excel but my purpose is somewhat is solved by VLOOKUP , MATCH, OFFSET, INDEX functions.
Prob: Here i have a database contains as below A1 B1 C1 D1 111 123 i/p o/p 222 301 111 321 333 456
If i keep 222 / 333 in C1 i will get output at D1 as 301 / 456 respectively. But if i keep 111 , i want a desired result to be most lastly updated value in the array that is 321 but it is printing 123.
let say if have A1 B1 C1 D1 111 123 i/p o/p 222 301 111 321 333 456 111 500
then if i enter 111 in C1 then i want to get 500 but still am getting 123 only. tht is the first hit. i want to return a value which has lastly updated.
So if i use any fuctions from above i may getting correct results for 222,333 but i want result for 111.
Thoughts and rules: 1.The new entry will added at the end of the array. 2. A1/B1 are only numbers. 3. free to use any functions
U can have flexibility to create one more array or even update the column B1 if any i/p matches in column A1. So there will always a single output for each unique input. Imean to say u can have a array with
A1 B1 C1 D1 111 321 (replace 123 with 321) i/p o/p 222 301 (remove this entry) 333 456
I have a number (22.3) in a cell that is there from an Hlookup function. I am then using a vlookup to lookup the 22.3 (from the hlookup) in a column. The number is in the column, but the vlookup function cant find it. If I were to type over my hlookup formula so that I am hand-typing in "22.3" instead of it being their from the hlookup, then the vlookup works just fine. I hope that is not too confusing. I dont understand what is going on. I checked to make sure none of my cells are rounding and therefore creating a look-alike number when it really isn't and that is not the case.
I have a Workbook with 18 "sheets and a recap sheet. Each sheet will have a different number of rows between A9 and N60. On my recap sheet I want to be able to find what the last row with data in Column A and then be able to do a vlookup on that row to display the data in Column 2,3,4, exc. I know how to do the vlookup's fine but I don't know how to specify to select the last row. The data will always be different and the number of rows will never be the same either.
I tried the below formula because everyone complains that if you use True is returns the last row but that didn't work.
=VLOOKUP(A2,Sheet_2!$A$9:Sheet_2!$N$60,2,TRUE)
Is there a way to do this without VB, I don't understand VB at all and that would get way over my head really quickly.
I am getting #N/A errors even when I have an apparently exact match in my table array to the lookup value. I know that excel requires the formats to be exactly the same and I can force the match if I copy the lookup value from my table array and paste it into the worksheet. Is there an easy way to "fix" my table array. The table array is a reference worksheet I have created which has worked in the past.
I haven't had issues with vlookup on other worksheets, but this one is giving me trouble. I have attached the excel file for your consideration.
The lookup value is in cell U2, formatted as a date The table array is V2:Z19 The column index number is 2 for C3 False lookup
I've tried the formula with and without the text function for the date and I've also tried different formats for the dates in columns V to Z because I know the format of the cells have to match for vlookup. It is very frustrating because this should be simple, but it is messing up.
Now, what i need to do is for each of the rows in my dataset, I need to find the reverse entries (consider first row where header 1 is 100 and header2 is 101, I need to search the entire dataset to find if I have some data for header1 is 101 and header2 is 100). In this case, I do have such data where header1 is 101 and header2 is 100.
Header1 Header2 Data Reverse Entry Difference 100 101 20.50 20.50 0 102 19 20 -1 103 1003.2 Not found Not found 101 100 20.50 20.50 0 200 300 30 270 102 100 20 19 1 200 101 30 300 -270
let me know if that's possible through excel formulas?
I need a column D with such data and column E for differences. So that, my final output should look like below -
Im using vlookup to search a table and fill out a calendar with dates that various employees are gone. They can have multiple trips in one month and thus will be in the table multiple times. I have the calendar set up so days go across the top and names go along hte left side. so each cell is identified by name and day. I then have each cell using the vlookup command to search the table. now if one person has multiple trips a month is there a way to compare multiple entries in the table?
generally vlookup function gives a value from the right side cell. is it is possible to display a cell from the left side. in the attached excel file i want the numbers against the cell which contains mom.....
I am working on a data with a range of same customer number but different sales figure. If there any way I can search for a duplicate cusomer number and then summing up the sales? I tried to use vlookup but it only recognise the first reference number. I have attached the excel for your reference.
i have a vlookup formula that i am using and i have two question
my first is something that i noticed while reading mrexcels book and comparing to what i was told here. i was refered to use column()+0 instead of what mrexcel says of col(the number)-how many to get the number i want.
now my setup is like this
in a1 i have a sheet named anaheim batting totals in a2-a16 i have week1-week16. now i have my formula that i enter in b2 that uses the reference in a1 to look up in the range in sheet week1( i manually entered the range location.) i then copy paste it down to b17 with the dbl click trick and then manually go into each of those formulas and change week1 to week2 to week 3 etc. i want to be able to reference the A column title(whichever week appears) and the a1 cell in the same vlookup formula. in essence to say lookup a1 in this range ex.a1:l450 in the sheet that is in the corresponding a column,i.e. week1.16 all set the same with the same range. the rest i can figure out, but i would like to have one formula to copy to all cells of my chart.
I love using vlookup, but what do I do if the value I'm looking up is listed more than once in my array? The default is that it will use the first value found.
I have a spreadsheet with 1000's of commodities and for each commodity the volume is broken out by month. For example, here's the sheet where I'm pulling the data from:
So my vlookup for the RC020 for the december Actual Quantity will default to the 5867316. For for January (period 2), if I do the vlookup it will still take the 5867316 again since its first in line.
So my question is, how do I alter my formula in January so it will vlookup the 2nd RC020 and give me the "4668300"?
I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).
Cell A1 contains a fuel cost.
Cell A2 contains a formula that returns a RangeName.
In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP($A$1,$A$2,2,FALSE). The result is #NA!.
If I use =VLOOKUP($A$1,INDIRECT($A$2),2,FALSE), I get #REF!.
Among other things, I've also tried =VLOOKUP($A$1,TEXT($A$2,"@"),2,FALSE). No cigar.
