is it possible to lookup the last 5 values?
I want to lookup letter "A", from a list of 300 parts. "A" may appear 30 times, but I only want to see the last 5 results (Sorted in chronological order).
If I have 5 cells, and perform a VLOOKUP it will always only return the first occurance of "A". I need to find a way of the second cell "forgetting" the first occurance and moving on to the next one and so on
im trying to get the lookup answer based on two values the current formula i have is
=VLOOKUP(AND(A1,B1),Sheet2!$A$1:$C$31,3,FALSE)
which naturally returns N/A... i might even be using the wrong formula?
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 have 3 Sheets named Paid, Rejected, and Reprocessed.
On the Paid and Rejected sheets I have 2 fields Customer # (Column A), and Amount (Column Q). (The customer # field has many duplicates but the amounts are never duplicates)
On the Reprocessed sheet I have all the rejected items (all fields) and also a field named Reprocessed. I need to use a formula that will check the Paid sheet for any items that have the same Customer # and Amount and return the amount
There are 8,216 rejected items and 45,047 paid items. Some items were originally rejected have been reprocessed and show under paid.
Any thoughts on which formula I should use?
Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:
1 A 14
1 B 22
2 C 84
4 D 25
I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies View RelatedI have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?
View 3 Replies View RelatedI tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
Can any one tell me out in getting the lookup value.
the file is attached here with.
I have a spreadsheet with two tables. Table1 (Summary) is my summary data, table2 (Data) is the array of data i need to pull from.
I am trying to create a monthly summary report. The values in table1 include values: date, creative element, revenue. I have both date and creative element defined in table-Data, I want the sum of revenue from each month entered into my summary report. My Data table has multiple values for each date/creative element combination.
Ex of my Data table:
Jan 2008 GoogleBase $1000
Feb 2008 Shopzilla $1500
Jan 2008 GoogleBase $1500
So if my data set was above, the summary of data for Jan 2008 for Googlebase would = $2500.
See attached w/Note. I would like to return the number in column B that corresponds to the value in column A that is closest to a specified number (input in cell A2).
View 7 Replies View RelatedA B CD
100 233 a 1
100 454 a 2
100 111 b 3
100 233 b 4
200 345 a 5
200 432 a 6
200 233 b 7
200 333 b 8
300 336 a 9
[code]....
I have values in 4 columns A, B, C & D. I have to get the values from cells in Column D corresponding to maximum & minimum value from column B when the corresponding cells in column A has either "100" or "300" & column C has a value "a". This means, if column A has either "100" or "300" & Column C has "a", then Column B has following corresponding values - 233, 454, 336, 654. Out of this the max. is 654 for which corresponding value in Column D is 10 (First output) & the min. is 233 for which corresponding value in Column D is 1 (Second output). Please note the value 233 & 654 is repeating in "200" & "400" range also.
I have a table that has 1000 people who have a duration of time associated with them. I need to get the names and times of the top 10 people. I would prefer to use formulas so that I can just make a template that I can paste my data into.
View 9 Replies View RelatedI am trying to create a table that references data in a much larger report that constantly has rows and columns added and deleted off of it. Here is an example:
---------------Juice Sell In-----Desert Sell in-----Total Sales
James--------34%---------------8%---------------22
Frank---------22%---------------2%---------------18
This is a simple example of what I would be trying look data up from. But there are hundreds or rows and columns in the actual report, so I am creating another document that just pulls the data I need to review off of the larger report. New rows and columns are added to the report on a daily basis. So I am trying to have a cell return a value as a result of an intersection of 2 other values that remain constant. For example: Looking up the intersection of the row that contains "James" and the column that contains "Total Sales" would return "22" as the result in the cell.
I want to match two different values in the same row and then have it return another value in that row.
I have a problem with a table I am using I need to look up two values in a table and return the the score to the relative cell.
I have attached an example of what I need. I don't know if I can use the VLOOKUP function or would it be better to use the Index feature?
I have defined a name called 'Com' which is for the whole table in the 'Com' sheet.
On the 'Results' sheet all cells highlighted in Yellow are where values are entered and contains no formula. The Green cells are the cells where I need to amend the formula so that it is more efficient.
What I need exactly is for the formulas in C4 to C11 is to first look up the values in column A and match the value in column B that relates to column A.
For example on the 'Results' sheet, if the value in B6 equals b then the formula in C6 should then look at the table in the 'Com' sheet then match 'Com 1' and then look at the response value in column B to match the rating of 'b' and then return the value of '4' from Cell C2 on the 'Com' sheet.
I am trying to lookup the value that corresponds to three sets of data. The formula I have been using is but I am still getting #NA. I’ve attached the spreadsheet
=INDEX($B$3:$I$17,MATCH($B21,$A$3:$A$17,0),MATCH($A$22,$B$1:$I$1,0)+MATCH($B$20,$B$2:$E$2,0)-1)
I am looking to search Vendor name 1, Actual and A to return the value of 1.
I have got a data file. A sample of the data is attached for convenience. Column A contains the name of the books in varioys rows, while column B, C & D contains the qty of the respective books in the given racks.
I want to search by book name likewise :
Qty of Book 1 in Rack1 = 10. Similarly if I want to search for Book2 in Rack3, what should be the formula for it. I have to use a combination of INDEX & MATCH function but somewhoe not able to get it correct.
I am trying to calculate a value by using two lookup statements in one formula. However, I get either #N/A, or very wrong results. Here is the formula:
=LOOKUP($B26,GrapePricing!$A$2:$A$15,GrapePricing!$B$2:$B$15)*
LOOKUP($B26,$B$5:$B$17,$D$5:$D$17)
What I need is to get the value in B26 (it is text, "Merlot"), go to the GrapePriceing sheet and find Merlot in range A2:A15, get the price of Merlot from the GrapePricing sheet range B2:B15, then multiply that by the matching value in the D5:D17 range.
I have a spreadsheet in which data is dumped from a database, there is a dump (on a new tab) for each site.
I need a summary sheet which will add up the two tabs. My problem is however is that I can not use Vlookup as the same codes are used and it will only return the first occurrence.
e.g.
444588 Prime Wages/salaries
This code appears under Production prime labour and Distribution Labour but will only ever return the production prime labour value
Another example would be
444586 Ohd Wages & salaries
This code appears under Production Overhead, Site Overhead, Sales overhead and Admin Overhead this would only ever return the production overhead value only
Is there any way that I can get the summary sheet to use a formula where it looks up the department and then the code? I was thinking index and match but I was unsure if this was possible also.
Worth noting is that the summary sheet has a list of all codes that could be used but the dumps will only ever have values if a cost has been occurred, there for the number of rows in each department is not consistent. There will be a number of tabs however in the attached example I have only provided two.
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.
I have two worksheets with different data in them. In worksheet "Sheet1"
A B C (?)
1 01 03
2 01 03
3 01 03
4 03 03
5 02 04
...
In worksheeet "Sheet2"
A B C
1 01 03 Mechanical Engineering
2 01 04 Aerospace Engineering
3 02 03 Electrical Engineering
4 02 04 Electronic Engineering
5 03 03 Hospitality
What i would like to do is to fill in the C column in Sheet1 by checking if the value for A and B columns match up with those in Sheet2, then copy the value in C column and put into C column in sheet1. for example if A1 = 01 and B1 = 03, then it is Mechanical Engineering. I was thinking of using VLOOKUP but don't think it'd take "if else" criteria.
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..
In my spreadsheet, one cell in every row has a string that contains the invoice number. I would like to get the invoice number in a cell of it's own. The problem is that different vendors use different lengths of characters for their inv #, so I can't do a one size fits all MID formula.
What would be the best way of getting the invoice number?
Can you use a V-lookup to find several different values and then sum them?
View 9 Replies View RelatedI am using vlookup to read a 3500 from the table below.
"Vd =65mph
R(ft)"e
126001.5
91302.0
82002.2
74302.4
67702.6
62002.8
57103.0
52803.2
48903.4
45403.6.......
My question is how do I write the formula so that it looks up a radius of 3500 by looking up a value slightly smaller than 3500, in this case 3440 and return the value of 4.4
I am not sure of the best way to ask how to do this. I am trying to efficiently lookup the X value in sheet A from the Y value in sheet B programatically as X=Y. The Values along the X and Y axis of sheet A change.
Sheet A...............................................
=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.
I have a workbook where road structure data is recorded i.e. bridges, culverts, etc.
One of the worksheets has data I need to lookup. The primary worksheet called “Structure_Data” has the ‘Road Number’ (RN) of a particular road and the ‘Road Running Distance’ (RRD) of a particular structure. A particular road may have many structures along its length.
Another worksheet called “Road_Number_of_Lanes” has data that that indicates how many lanes there are between particular RRDs.
Here is what I am trying to work out...
In the “Structure_Data” worksheet look at the RRD of a structure and the number of the road it is on, then look up the road number on the Road_Number_of_Lanes worksheet; look at the ‘Start RRD’ and the ‘End RRD’ and return the number of lanes for that particular RRD.
“Structure_Data” worksheet
Column C has the Road Number
Column X has the Road Running Distance
“Road_Number_of_Lanes” worksheet
Column A has the Road Number
Column C has the Start Road Running Distance
Column D has the End Road Running Distance
Column E has the number of traffic lanes
My problem is that for example Road Number 1000 starts at 0 and goes to 927.09 (approx 927 km long). There are 27 entries of RN 1000 i.e. from 0 to 411.69 there are 2 lanes, from 773.62 to 774.85 there are 3 lanes and from 774.85 to 778.09 it goes back to 2 lanes.
How can I find out how many lanes of traffic there is at a particular structures location.
In D16:D29 I use vlookup to return values from named range 'mforcetable' based on value in cell C16.
However, if values are entered in cells I36 & K36, I37 & K37 and I38 & K38 I need the lookup to return the values in 'mforcetable' including and between the 2 values.
See attached example.
Based on value 2000 in C16, what I need to see in D16:D29 is
10.0
20.0
40.0
100.0
100.0
200.0
400.0
600.0
800.0
1,200.0
1,600.0
1,600.0
2,000.0
0.0
Where the 100.0 and 1,600.0 are repeated because both values are specified in I36 & K36, I37 & K37 or I38 & K38.