i have a couple of columns (J2:K393) and a cell (O2) elsewhere.
i need to look for the max and min values of that cell within my 2 columns.
this formula (i thought it would work) doesn't return the correct number.
what am i doing wrong?
=MAX(VLOOKUP(O2,$J$2:$K$393,2,FALSE))
=MIN(VLOOKUP(O2,$J$2:$K$393,2,FALSE))
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
Excel 2002
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 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 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.
I have a dataset that looks like below -
Header1 Header2 Data
100 101 20.50
102 19
103 1003.2
101 100 20.50
200 300
102 100 20
200 104 30
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?
View 14 Replies View RelatedI need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below
a b c d
1 Tom 333
2 Sam 22
3 Sam 22
4 John 5
5 Sam 22
6 Sam 1
7 Tom 3
8 Tom 333
So the answer would be
Tom = 2
Sam = 2
John = 1
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies View RelatedI'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedI’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies View RelatedI have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.
I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?
I'm taking a spreadsheet that I produce each month and creating a year to date spreadsheet in the same format. I'm using a vlookup to find the campaign name in each sheet and add up the totals. This works fine but sometimes a camapign ends and so the vlookup for that month will produce an #n/a value so will reduce the whole sum to #n/a.
The VLOOKUP + VLOOKUP + VLOOKUP I was using that produced an #n/a is shown below.
=VLOOKUP($A6,'[Margin by Site Net April 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net May 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net June 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)
To get round it I've added in an IF statement combined with ISERROR as shown below. It works but is looking quite messy. Is there an easier way to do this ? (the formula below is from the cell below the one above so the look up value is one cell down)
I have sheet with rows and column from cell(1,1) to Cell(8,18) and it has #N/A inside these data. Is there a way to use Find function to find #N/A and replace it with empty space?
View 11 Replies View RelatedI am working on a macro that has a VLookup in it.
The sheet that this will be applied to comes in weekly and can have anywhere from 10K to 30K.
I want the VLookup equation to be able to find the last possible row with data in column A and then copy the Vlookp equation in column B to the last row.
Can someone please provide the correct code that will allow me to do this?
What formula will return the the first value to the right?
For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1).
In my code I have a very useful line:
[other code stuff here]...Range("J" & Rows.Count).End(xlUp))
That helps me find the value in the last row that actually has data in my spreadsheet. This is great, but I want to try to accomplish the same thing without vba. In other words, in cell A3, I always want the value that is in the J column, but the last row that has data. (In other words sometimes the last data row ia 97, sometimes it is 23.) Since the value is always changing this won't be vlookup. Is there another excel function that can find the end without the use of vba?
This might seem goofy to need to do since I already know how I can in vba, but in my (admittedly pathetic code) I keep have an endless loop and if I can figure out a way to get this value on the ss.
I need to find, using VBA, three highest numbers out of entire column (L), select 13 appropriate ranges like for L9 - A9:M9 , for L8 - A8:M8 and for L7 - A7:M7 and copy to a different sheet.
View 9 Replies View RelatedBook1.xlsx
I have two sheets of data and two tables (1=finial 2=data comparison) I am trying to figure out some kind of formula or vba that can easily tell me that the location point in table one data comparison matches the location in table two. The VPMDFS (location footage) in table two or NDE data can be a range, say vpmdfs.NDE + or - 3 ft. so the ILI or source record should fall in between this range. Once the match has been made I need to copy and paste that record onto sheet one all in the same row. I have been scratching my head and researching for weeks and cannot come up with a simple solution. My problem with all the scenarios that I've tried is the formulas what the data in the data comparison sheet to already be in the same row. Please reference my attached work book for examples.
How to find a particular cell value, example: i have sheet and in that we have 30 employee names and i need to map the data of work from home, which is in dark bule color and need to make a total of it. rather than counting the all the sheet details from month start till end. I need to find it in a quick shot.
I would like to prepare a shift schedule for 3 months it should be a automated.
I'm currently using a formula which is exactly what i want but i need it to do something additional.
The formula is: =IF(ISNUMBER(SEARCH("Level 1 ",$I:$I)),$K:$K,"")
My understanding of this formula is that the cell the formula is in then searches column I for the value "Level 1 ", if it's true it returns what is in column K, if false then it returns the cell blank/0. This is a great function and works well.
What i want to know, is there a way to add another rule on to it that instead of returning the value blank/0, it finds the next cell in the column that has a TRUE value and omits it instead.
For example:
At the moment it shows:
Level 1A
Level 1B
Level 1C
(Blank Cell)
Level 1D
(Blank Cell)
Level 1E
But i want it to show like this:
Level 1A
Level 1B
Level 1C
Level 1D (Replaces blank cell with the next TRUE value)
Level 1E (Replaces cell that previously had "Level 1D" in it to the next TRUE value seeing as "Level 1D" is now above)
I don't want the cell to appear as blank.
In column K, i would only have the data for "Level 1 ", i can then copy the formula to other columns for "Level 2 " and so on. I don't need it to return the cells in alphabetical order, just the next true value in the column that matches the values in the reference column.
I have a working formula that gives me the last value in a column using Index formula below. How can i slightly adapt the formula to find the maximum value in that column instead?
=LOOKUP(10^99,INDEX('Data Drop'!$A$15:$AZ$10000,,MATCH($H$9,'Data Drop'!$A$15:$AZ$15,0)))