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


Returning Text From Nested IF And Vlookup Statements

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

Vlookup Values In 2 Columns Returning Value From The Third Column

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

Returning Row / Column Number Of Cell That Contains Particular Text

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

Referring To An Array Column Or Row Of LinEst Function

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

Vlookup - Match All Text In Column

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

Check Column For Existence Of Text Before VLOOKUP

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

Vlookup Keeps Returning #ref

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

VLOOKUP - Returning '#N/A'

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

Vlookup Returning #value!

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

Returning 0 Value With VLOOKUP

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

VLookup Returning #N/A

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

Vlookup Not Returning A Value (vba)

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

Vlookup Returning #N/A!

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

VLOOKUP Returning Incorrect Value

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

VLOOKUP Returning Wrong Value?

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.


View 4 Replies View Related

VLookup, Returning A Value On A Vector

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.


=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

VLookup Without Returning Cell In Same Row?

Jun 24, 2013

view the graph below:






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.





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

Vlookup Formula Returning #N/A

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

Vlookup Returning #N/A When It Shouldn't

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

VLOOKUP Returning Wrong Value

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

Vlookup Returning #N/A When Value Exists

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

Match To Columns To Each Other Before Returning VLookup

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

VLOOKUP And Returning Multiple Values

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

Vlookup Returning The Wrong Data?

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

Doing A VLOOKUP And Returning Multiple Entries

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

Vlookup: Returning Multiple Matches

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

Vlookup Returning The Closest Match Down

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

Using A Vlookup And Returning A Cell Reference

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

Copyrights 2005-15 www.BigResource.com, All rights reserved