Vlookup - Referring To A Column Other Than The First And Returning Text
Sep 28, 2006
I have two worksheets. On one worksheet will be a place to enter a five digit number. In the adjacent cell, I want to search on the second worksheet in a three-column area for that same five digit number. The five-digit numbers on the second worksheet are located in the third column and the text I want returned from the second column. I cannot change the order of the columns as the first worksheet will be an "add-on" worksheet to a widely used spreadsheet within our company (this spreadsheet already has the second worksheet formatted).
View 9 Replies
ADVERTISEMENT
Jul 26, 2006
I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.
Example: ....
View 14 Replies
View Related
Oct 6, 2009
My Data
Service 1 | 100 | 1.5$
Service 1 | 200| 2.5$
Service 1 | 300| 3.5$
Service 2 | 100| 11.5$
Service 2 | 125| 21.5$
Service 2 | 300| 30$
I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.
I tried SumProduct but the problem with my formula was:
Let's assum I ask for
Service 1, 200
The formula summed up 1.5 and 3.5 and returned 5
When I wanted to return one value from this table.
View 9 Replies
View Related
Jul 16, 2014
I am looking to write a formula that will return the row or column number of the cell that contains a particular text within the string. I know it can be done with script, but need it as a formula. Also, to add another level of difficulty, I'd like it to be a traditional formula, no special keystroke to activate. It will be used within a much larger formula.Ex. If G5 has the string "CURRENT DATE" in it, and I look for "DATE", the formula would return 7 for the column or 5 for the row.
View 4 Replies
View Related
Jan 13, 2010
I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format “A1:A45”. Is there a way to refer to a column in an existing array instead?
For example (thanks to Shg for this code): ....
View 7 Replies
View Related
Feb 12, 2007
I have a worksheet that has over 5000 rows - what i need is to find all rows that contain " Total - content targeting" in column 3 & add the number on each corresponding row (column 3 from text match). Below is what i have managed to create,
=VLOOKUP("Total - content targeting",'Google Traffic UK'!C7:Q5000,3,FALSE)
However this only finds the first instance on "Total - content targeting". My speadsheet contains several of these which i need to add up.
how i might change my code to allow all instances of Content targeting to add?
View 8 Replies
View Related
Jun 21, 2008
I need to perform a lookup with multiple criteria.
I am attaching a sample file.
Sheet 1 contains 3 columns
A - Report
B - Name
C - Date
Sheet 2 contains 2 columns
A - Name
B - Date (where results are to be placed)
Basically, I would like column B, Sheet 2 to be populated with the Date from Sheet 1, column c if . . . .
2 criteria
1) column A, sheet 2 matches column B, sheet 1 and
2) column A, sheet 1 = ABC
I keep getting a FALSE or #N/A results.
View 9 Replies
View Related
Nov 23, 2008
i have a drop down list in cell b16 which then corresponds with what to bring up in the next drop down list in cell d29 this works fine.
View 2 Replies
View Related
Jan 22, 2009
I have just decided to dable in the 'VLOOKUP' function. I have looked at a few examples on the net and I have tried to replicate them with no luck (I have modified titles and values). I have attached my example to this post.
View 2 Replies
View Related
Oct 11, 2009
=IF(ISERROR(VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,11,FALSE)),"",VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,10,FALSE))*D14
As long as the lookup value is true the formula works but if the lookup value is false then I get #value! when it tries to multiply by D14. How do i get the formula to return a blank cell or 0 value if the lookup value is false?
View 5 Replies
View Related
May 20, 2009
I'm hoping someone can help. I've written a =VLOOKUP formula that works fine. What I'd like to do is if the value can't be found within the lookup then the formula returns a 0 rather than the standard #N/A. Can these be joined up in 1 formula?
View 6 Replies
View Related
Nov 13, 2006
I have a VLOOKUP formula that is working just fine in Column O.. When I drag it over to Column P (changing nothing in the formula only changing the month) why would it now return #N/A. There is nothing wrong with the source that I can see..
View 9 Replies
View Related
Oct 14, 2006
the following vlookup is not returning a value in the specified cell (C39). From the threads I have searched through it looks OK but not working
Sheets("Day 1").Range("C39") = Application.WorksheetFunction.VLookup(Sheets("Day 1").Range("M2"), Sheets("PCO").Range("B1:C12"), 2, False)
Im sure from past experience I have missed a comma or something but buggered if I can find it!
View 5 Replies
View Related
May 26, 2007
I have a problem with a table, works fine for the first five rows then doesn't work afterwards. Can anybody see what I'm doing wrong?
(File attached)
View 9 Replies
View Related
Nov 28, 2012
I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.
My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.
View 4 Replies
View Related
Jul 3, 2013
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
View 9 Replies
View Related
Jun 23, 2014
I am using vlookup to return data that i have hyperlinked in another sheet. But when it returns the data, the hyperlink has gone.
VLOOKUP HYPERLINK RETURN PROBLEM.xlsx
View 4 Replies
View Related
Apr 9, 2008
I want to use the V lookup function to return a value, but rather than returning the value in the same row as the match in the specified column, I want to return the value in the cell below.
Eg.
=VLOOKUP(B397, A$267:H$351, 7, FALSE)
... but rather than returning the value in column 7 on the row that matched with B397, I want the field below.
View 9 Replies
View Related
Jun 24, 2013
view the graph below:
A
B
1
EXTENSION 101
2
INBOUND TALK TIME
55M19S
3
OUTBOUND TALK TIME
1H22M13S
[code].....
I am trying to create a formula that will act like a vlookup but will return a different row. In other words, On page two I need to write a formula that says: In column 1 if you see "Extension 101" return column 2 of the row right beneath it, so on and so forth. So page two should return a mirror of what page one says, the difference is I would like people to type in "Extension 101" in a cell and it would return all the date.
A
B
1
EXTENSION 101
2
INBOUND TALK TIME
55M19S
[code].....
Cell A1 would be typed in//Cell A2-A4 would be static//Cell B2-B4 would return these numbers based on info from A1.
Cell A1 would always be unique in column A (on both pages).
View 4 Replies
View Related
Oct 5, 2007
I have the following Vlookup formula:
=VLOOKUP(G3,'Calculation for September 07'!C7:E1975,2,FALSE)
The formula is returning #N/A, but I see the lookup value in the table array.
What do I need to add to the formula to have the lookup value recognized?
View 9 Replies
View Related
Apr 2, 2008
i sell solar panel systems and i've broken it down to 18 different packs that i can sell, the reason for this is that i can very quickly have a tailored quote for a particular client. what i want to do is is literally from a pull down menu have the 18 packs, pick one and for all elements of that quote to come up. i have used a data validation list for the pull down menu which works fine. for the 18 packs, each one has an array of info of between 15 - 18 rows and 4 columns wide (containg info like part description in first column, then cost in second column, quantity in third column and total cost in fourth. how i set it up was, i have all the seperate packs going for left to right on a spread sheet with the title name on top. i have created a quote area so when i use the pul down menu to get title all that pack info should come up. i have done this by using the hlookup command to return the first column on the left - description on parts and this works fine - then to return the cost in the second column, i am using a vlookup command by looking for the description on the left to return the cost. this works for 90% of the info and for the other 10% i get an #N/A when i know it shouldn't. all cells have the same format so i know this isn't the problem. i would really appreciate any help on this from anyone who has had this problem before.
View 9 Replies
View Related
Dec 14, 2006
I have this formula that returns a grade mark for a specific score in a test:
=VLOOKUP(D4,'Grade scale'!$A$34:$B$42,2)
using these values in the lookup table:
Low scoreGrade
0.0% U
25.0% G
35.0% F
45.3% E
55.3% D
65.5% C
72.0% B
78.0% A
85.0% A*
The problem comes where the Grade is A or A* as the Vlookup table can't differentiate the two. Anyone know how to modify the formula so that A and A* are seen as distinct values?
View 9 Replies
View Related
Jan 20, 2008
I feel like I've tried everything on the attached spreadsheet to get the vlookup to pull back the schools code.
View 2 Replies
View Related
Apr 10, 2014
Not sure where to start with this one, an IF statement or vlookup? Basically I have 3 columns A, B, C (original sheet) and A, B, C in my working sheet. A contains car registrations, B contains a date and C contains a weight. I need to return C from my original sheet into my working sheet only when A and B BOTH match from my working sheet (there are scenarios where the same car registration has a different date next to it). So I want to return the weight only when the car registration and the date match in a specific instance from my working sheet.
View 6 Replies
View Related
Dec 15, 2013
Trying to pull data from one workbook to another using Vlookup.
The worksheet has 7 columns (date, day of week, time or arrival, package, departure time, patron count and group name) I would like to pull all the data for a specific date in a seperate workbook.
I know I require a nested formula but cannot seem to get the variables correct:
View 2 Replies
View Related
Jul 10, 2014
I am working on a form to pull employee identifiers such as employee ID, store number as well as sales performance. I am using the below formula but the data that is being returned is not the correct data for the specific employee. C5 is the employee name (last name, first name) in a single cell. A sample of the spreadsheet I am trying to pull data from is attached. Some employees the formula pulls the right data and some it does not...
=IF(C5="","",VLOOKUP(C5,'Raw Data'!A3:N15,2))
View 3 Replies
View Related
Jan 30, 2009
I've tried to amend the formula's posted above but to no avail so am hoping someone can have a look at the attached.
I would like to be able to use Vlookup or similar to complete a table (starting in cell A19) based on the surname added in B16.
View 2 Replies
View Related
Jun 22, 2009
I am trying to connect a special date and a price to a special name (please see the attached file).
In sheet 1 I have the following:
- In column A I have a set of different names.
- In column B I have a set of different dates.
- In column C I have a set of different prices on bonds that off course refers to the different names at their respective dates.
In rest of the sheets I want an overview of the different bonds, and what their prices are at the exact date. The problem is that if I use the vlookup it is only the first value that will be extracted. How can I get all of the prices corresponding to the different dates to the correct bond. I also want the possibility to update this every day.
View 3 Replies
View Related
Dec 8, 2009
I'm using vlookup to get a price for a unit of measure and it passes the value of the lesser not the greater. Is there anyway to get vlookup to pick the closest match as the greater value not the lesser one?
For example:
1000 units costs $8,000
4000 units cost $23,000
12000 units cost $68,000
If someone wants to buy 2500 units, I'd like the vlookup to pass the price based on 4000 units, but it passes 8,000 the value on 1000 units.
Can I use vlookup to give me the result of the greater unit not the lesser?
View 4 Replies
View Related
Nov 14, 2008
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",......
However this appears not to work with a vlookup.
View 10 Replies
View Related
