Vlookup in Excel

I have a random problem with vlookup that I've not been able to figure out.
Sometimes when I try to lookup a value on a different workbook using vlookup,
the formula disappears when I swich to the other workbook. I'm wondering if
this has something to do with the format that the workbooks are save in. To
work around this problem I end up having to copy the worksheet from the other
workbook and paste it into the workbook I'm doing the vlookup in. This
works, but is rather annoying. What causes this problem, and what's the
solution?

I am having a problem using VLOOKUP when using a date and time. The Column "
Date TIME" has formatted cells in the form of MM/DD/YY HH:MM. In Column B (#
MSGS) I have a value in this case it is message counts.
I have a second Table consisting of "Date Time" Column also in the fomrat of
MM/DD/YY HH:MM. I want to match the "Date Time" Value in my Second Table
against the "Date Time" Value of my first table and extract the value of "#
Msgs" of this First table and store it in the MSGS column of the second table)

this is the formula I am using
=VLOOKUP(A1,TSAT_28JUL05_Activity!$B$5:$C$706,2,FALSE)

Basically A1 is the cell of my second table "Date Time" in this case and
TSAT_28JUL05_Activity!$B$5:$C$706 are the Date/Time and Msgs of my first
table. What am I doing wrong? I appreciate your help. Thank You!
First Table

Date Time # Msgs
7/21/05 0:03 1
7/21/05 0:05 15
7/21/05 0:06 3
7/21/05 0:07 1
7/21/05 0:10 3
7/21/05 0:11 6
7/21/05 0:15 5
7/21/05 0:16 5
7/21/05 0:20 5



Second Table

Date Time Count
7/21/05 0:01
7/21/05 0:02
7/21/05 0:03
7/21/05 0:04
7/21/05 0:05
7/21/05 0:06
7/21/05 0:07
7/21/05 0:08
7/21/05 0:09
7/21/05 0:10
7/21/05 0:11
7/21/05 0:12

My spreadsheet has a VLOOKUP through 44,000 rows that only needed to be
performed once. The relevant data is in the VLOOKUP cells as I want them and
the lookup values will never change. As long as I keep the other worksheet
intact, can I convert the VLOOKUP formulas to absolute references? My
spreadsheet is running very slow with the VLOOKUP in place, so I'm trying to
speed things up. Would this idea work?

Hi,
Can anyone advise me how I do a VLOOKUP if a cell Contains a certain word.
The cells contain various sizes and either Standard, Secure or Secure Plus.
i.e.

4Mbit/s Standard
4Mbit/s Secure
4Mbit/s Secure Plus
6Mbit/s Standard
6Mbit/s Secure
6Mbit/s Secure Plus
8Mbit/s Standard
8Mbit/s Secure
8Mbit/s Secure Plus
10Mbit/s Standard
10Mbit/s Secure
10Mbit/s Secure Plus
15Mbit/s Standard
15Mbit/s Secure

What I need to do is if the cell selected contains say "Secure Plus", then
do a lookup.

Thanks

DAWN

I have set a VLOOKUP formula to match account numbers. How ever I have to
click double click on the cell before the formula recognises the account
number. I have to do this for each cell. I have tried different formats. It
is not saving me that much time.

Hi,
Is it possible with VBA to enter the Table_Array of a Vlookup with an
Inputbox?
My problem is that the vlookup that I run needs a table_Array that could
resides in several directories.

--
Regards,
Jeff

i cant get vlookup to select the closest value if the closest value is higher
it automatically selects the highest value below the cell value

Hi!
I use vlookup to fetch text from cells on a separate sheet and it works just
fine. The problem though is that I want to get the format of the fetched text
also e.g. If the text in all cells is blue on sheet2 and I want to fetch it
with vlookup to sheet1, then I only get the text and the text is not blue!
The same thing is if the text is in italics, then vlookup stills return the
text i normal font. Is there a way to make vlookup return the format of the
fetched cell??

I have the formula:

=VLOOKUP($D$4,Database!$A$1:$AA$653,14,FALSE)

Where in the destination cell, I want a picture.
In other words, instead of taking the text "See Diagram", I want my actual
picture in there.
Is it possible?
Thanks,
Joe

I did copy subtotal value only by edit/go to/ special/visiable cell to the
sheet A. It shown part no. 31611 Total - qty 114. Then I did "find" replace
"Total" with blank field, so I can get part no. only. => part no. 31611 , qty
114 - they both on 2 different column.

Now I can't Vlookup these 2 column data. I need vlookup qty column. It
shown "#N/A" when I loopup qty column. Is this text / number issue? How can
loopup value from subtotal result?

Is subtotals value a text field ? Why can't I vlookup the value from other
worksheet?

Any solution? so I can lookup the subtotals value.

Need helps urgently...

Thank you,

Hi!

I just want to ask how can I use VLOOKUP() to look a match range to another range but instead of it looking for the EXACT values, I want to look for "LIKE" values. Example:

Column A
FIRE
ICE
WIND
WATER

Column B
FIRE WORKS
ICE BERG
WIND VANE
WATER BASE

As you can see, if I use VLOOKUP() in here it wouldn't find any match. What I want is to do is look up Column A to Column B and find matches if their values are similar to each other.


Thank you.

I need help with a problem I have please.

I have two separate sheets A and B.

I am trying to count the number of times a product appears in sheet B and the return this value in a cell in sheet A. sounds simple enough.

However a simple VLookup function is not working because the value that has to return to the cell in sheet A has to match 2 criteria along an X and Y axis.

I am trying to count the number of times the product appears in sheet B relating to a specific placement and bring the value back to Sheet A in the specific cell.

Here is an example of what I mean as the sheet i am working from has thousands of rows:

Sheet A

Product 2 Product 3 Product 4



Placement 1 value? value? value?

Placement 2 value? value? value?

Placement 3 value? value? value?




Sheet B


Placement 3 Product. 5

Placement 2 Product. 3

Placement 1 Product 3

Placement 2 Product. 3

Placement 3 Product 2

Placement 3 Product 2

Placement 1 Product 2

Placement 2 Product 4

Placement 3 Product 4

Any assistance would be very much appreciated.

Thanks,

Adrian

Hi - I'm trying to get the average for a set of values distingushed from the main group by a value in a corresponding column.
Presumably I need a lookup function but can't figure out quite how to do it.
e.g.
#1 #2
a 12
a 10
a 9
b 24
b 26
c 17
c 14
a 16
Q. How do i get the average for all of the values correpsonding to a's b's and c's?

Cheers
Poolio

Hi All

I am using a vlookup simple. The vlook up works on some cells but others it wont.

The data its looking up right... i can make them all work BUT i have to go through and double click into the text of the cell its looking up and then just push enter. like forcing it to realise its there or somethign. then the vlookup works?

rather than giong through each cell and double clicking it is there a quicker way i can do it? through a macro or something? i tried to record one but it has the cell specific values when i do it so it wont work.

i hope that makes sense.

Cheers

Joel

Hi All,

I am currently using a vlookup to display financial information for the year.

I'm using ISNA to remove #N/A when results arent available but I would also like to remove the number "0" when this result is returned as I would like to run an AVERAGE formula for the months with data and obviously the 0 skews this result.

The formula I currently use is:

Quote:

=IF(ISNA(VLOOKUP(B4,'[2008 Client Portfolio.xls]Sheet1'!$A$5:$S$1319,8,FALSE)),"",VLOOKUP(B4,'[2008 Client Portfolio.xls]Sheet1'!$A$5:$S$1319,8,FALSE))

Thanks for your help

is there a way to create a vlookup referencing the tab name?

For example, I have a summary sheet and tabs labeled 1- 200. I would like to create a formula on the summary sheet that says - if the # on the summary sheet corresponds with the tab name, then grab the info from that tab.

Hope this makes sense. Any help would be appreciated. This could save me a lot of time that I would spend linking sheets!

Thanks in advance!

I have a Vlookup forumla running on a spreadsheet I use at work. I expanded the table today to reflect new changes but the formula is not returning the correct value.

Current Formula
=VLOOKUP(Section2Sum,Section2TableLookup,2)

the Section2Sum is the returned value and due to the new changes this value was not included in the Section2TableLookup. I added the value to the Section2TableLookup and its correct value to be returned in the second column.

when I attempt to test the changes the Section2Sum is coming back as 29.66 which is correct, however it returns a value of 11 which is listed in the Section2TableLookup under a value of 27 not 29.66. What I don't understand is how the formula can be searching for 29.66 and return the listed value for 27.

I have verified that the unique references are looking at the correct ranges... that is to say I did adjust Section2TableLookup so that it looks at the entire table as i had to add 1 new row to the table to input the new value.

I am useing MS Excel 2003... any advise would be greatly appreciated as I need to get this working asap.


GoldenWing

Hi

I am trying to obtain a lookup result using the Vlookup function using 3 sheets of data - Sheet 1 being the parent sheet (where the formula will sit) , Sheet 2 and Sheet 3 being the child (data), however I am unable to find the correct combination.

On the parent (sheet 1) I have one column showing the membership number of our book club i.e Column E MEM12345. I want the adjacent column F on each row to show either that the member has recently joined using the new membership number data from (sheet 2) or is an existing member from the data on (sheet3) by returning the values "New Member" or "Existing Member"

I am familiar in the standard VLOOKUP formula eg. =IF(VLOOKUP(A2,Joined!A:A,1,FALSE),"New Member") to return the value I want using only 1 sheet, however I am unsure on how to combine the same formula that would also reference both sheets to return either values

An example of what I have tried is below, however it is showing an error.

=IF(ISNA(VLOOKUP(A4,Joined!A:A,1,FALSE),"New Member",VLOOKUP(A4,Existing!A:A,1,FALSE),"Existing Member")

Can someone please advise of the correct structure of the formula or an alternative way of acheiving the result?

Regards

Alan

Evening all

I was wondering if anyone could help with this thing I need?

Before I try to explain the bigger problem and confuse myself (cos it's not hard), i'll ask a simpler question.

Im doing a vlookup that is returing the value of a cell which is a hyperlink but it will only return the text and not the hyperlink itself, is there anyway of getting it to show the hyperlink?

Cheers peeps

C
x

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?

In Column A I have vessel departure dates
In Column B I have Final Destinations
In Column D I have vessel arrival dates

I am trying to get the result of column D based on matching A & B information.

My dilemma is that if I have vessels with the same departure dates going to different destinations how can I retrieve the arrival date?

I've been trying "vlookup" and "index/match" to no avail.

I've posted a sample file. If anyone has a chance to take a quick look I'd appreciate it.

Hiya,


There is a table with headings A1:F1, and numerous rows underneath (A2:F20). Under the headings, some numbers or text are typed under the headings, but not all fields are completed (many are left blank).

At a separate part of the spreadsheet, the user selects one of the headings (using a data validation list) in Cell B30. Then cells B32:B50 (corresonding to rows 2:20) will display "YES" if the corresponding field in the table HAS DATA ENTERED, and "NO" if it doesn't.

Hope this makes sense and any help is much appreciated!

Thanks

Good day,
I have filled the following formula in many cells of a spreadsheet.

=IF(ISERROR(VLOOKUP(D75,'Sheet1'!$D$3:$D$5442,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75,'Sheet1'!$C$3:$C$54 42,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE)),VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE),VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE))))

I am wondering if I have nested too many functions for this to work properly. In the case where one of the first two if statements are true (an error would be produced), I am receiving a "0" as specified. However, in the case where the statement VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE) results in an error then I should have the return of VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE), but am getting a #REF! error instead.

Any thoughts?

Thank you in advance,
N

Hello,

I am using a VLOOKUP formula incorporating the INDIRECT function and want to replace the indirect function with something else, perhaps a match, index, choose, offset or some other function. I cannot figure out a way to write this formula with a different function and need help. Here is a link to my file.

The following formula in cell B7 is:

=VLOOKUP(C4,INDIRECT(N7),2)

Cell C4 is a value greater than 1.

The formula in cell N7 is:

=VLOOKUP(NUM,DATA,7)

NUM is cell C2 and can be a value greater than 1. The contents of range name DATA is

1 Asample D E F G AAA
2 Bsample D E F G BBB

This table can have up to 100 rows and 50 different values in column 7.

The formula in N7 therefore returns AAA or BBB by matching the value of NUM in range DATA and looking up the contents of column 7. AAA or BBB are range names of other tables which are used in cell B7 thanks to the INDIRECT function. Using this function B7 does a lookup in table AAA or BBB.

Table AAA looks like this

0 5.75%
100,000 4.75%
250,000 3.75%
500,000 2.00%
1,000,000 0.00%

Cell B7 will return a value equal to =LOOKUP(C4,BBB,2). If cell C4 is equal to 125,000 then the value returned in B7 is 4.75%. I cannot hardcode the range name of the table because the user can change the value of NUM.

Any suggestions?

Thanks for your help!!

I am setting up a temporary inventory management spreadsheet until we have new software installed. My main issue is 'automating' stock receipts.

On sheet 1 I have part #, current stock holding and qty on order. When I receive a delivery I need to increase the stock holding quantity by the amount received.

On sheet 2 I enter the part # and the quantity received. A vlookup runs to sheet 1 against part # and returns the current stock holding + what has been received. I now have my new stock holding. I just need to find a way to get it back onto sheet 1.

Would a combination of a vlookup and a macro be the answer here?

Hey all,

Been trying to figure this one out but am just stumped. I have to do a vlookup to a sheet which will not have a consistent file name. I'm pretty sure it will always contain the characters 'abc'. The file will be open, so how can I use a wildcard to reference this sheet? I read about using indirect for this, but I can't seem to quite get it.

Thanks!

Hello,

Can anyone tell me if is possible to copy data from sheet1 to sheet2 using VLOOKUP?

I had an interview where they asked me to copy data from sheet1 to sheet2.
I used the function (=Sheet1!A1:...) and for me it was right, but they told me that it was wrong since I did not use the (VLOOKUP) function.

Please confirm if VLOOKUP can be used in this regard.

Thanks in advance,
Realty

This is Neaz. And this is my very first post in excelforum.

Here goes the scenario. Please try to understand first:

Sheet1:
Column A--- Column B--- Column C
Neaz ---------- math ----------- 95
Jean -------- Geography -------- 75
John ---------- English ----------- 85
Reese --------- physics ---------- 90

Sheet2:Column A
John
Reese

What I want is that I want to check whether Column A of Sheet 2 matches with Column A of Sheet 1. Then I want to get deleted those cells from Column A of Sheet 1 which matches with Column A of Sheet 2. Or, I want to get deleted the whole row of those cells from Column A of Sheet 1 that matches with Column A of Sheet 2.

Therefore the outcome of Column A of Sheet 1 will be:

Column A --- Column B --- Column C
Neaz ----------- math ---------- 95
Jean ------- Geography --------- 75

I hope i'm clear.

I've used a vlookup formula and we have a #N/A in that cell prior to it's lookup. I'm trying to conditional format that cell so that we cannot see the "#N/A".

Can anyone lead me to a solution as to how I can hide the "#N/A" either by using conditional formatting or another way.

Thanks

Hi,

I have put a VLOOKUP in place for a range of cells. Where the referenced cell has no entry it puts #N/A in the sheet. This is causing me further problems, is there a way to get all entries which equal this to leave the cell blank?

Thanks in advance for any help

Hi guys, hopefully you can shed some light onto this small problem I am having.

The current formula I'm trying to use is:

=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))

This isn't producing the desired results.

What I am trying to do in words is this:

IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail

I think I've gotten the syntax mixed up...can anyone guide me?

I would like to creat some sort of formula similar to a VLOOKUP where a cell reference to a dropdown list can be used to alter the location of where to cross reference.

I have multiple name defined ranges in a dropdown which would be used for the table array part of the formula. However, I only get errors with the current formula. Hope this makes sense. I have attached an example that might help.

Thanks,
Mike

I have excel 2003 and am having problems with integers on the lookup function.
I have to compare two lists of approx 33,000 values, some text and some integers. The lists have have all been formated as text, but when I use the lookup function, all number only part numbers don't find their match.
What can I do?

Hi everyone,

I'm trying to do a vlookup on a non contiguous named range.

I have a non contiguous named Range called "temp".
Code:

=Sheet1!$A$1:$B$3,Sheet1!$A$5:$B$7

I'm trying to perform a vlookup using this range by I'm getting an #N/A error
Code:

=VLOOKUP(D1,temp,2,FALSE)

Any ideas how this works?
Thanks.

I am trying to write a formula that uses both an if statement and a vlookup statement.

It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.

So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")

But it doesn't work. Is it possible to nest a vlookup in an if statement?

Hello!

Pls. I tried searching for a solution to my formula problem in this forum regarding vlookup and saw similar formula e.i returning multiple values but it doesn't cater with what I need.

Vlook is returning only the first value but what I need is to return all the multiple return values in 1 cell. I've attached a sample worksheet for reference.

Thank you!

I have a very simple pivot table that I am trying to use as a data source for a VLOOKUP request. Unfortunately, every time I do it I get the result #NA. I have tried recreating the results from the pivot table in the worksheet next to it, and am able to use VLOOKUP on them successfully, but as soon as I point the VLOOKUP array to the pivot table, I get the #NA result

Can VLOOKUP be used on pivot tables? If so, is there any special syntax I need to use?

Thanks

Hi

I hope someone can help me out on this as I've been searching on the internet for a solution for a long time without success.

I know someone has probably asked similar question before but the search has brought up too many to go through.

I'm looking to link from a cell in one worksheet to another based on the matching value using the Hyperlink function. I am aware this can be done manualyy through the Insert Hyperlink on the Excel Menu but this would be to painful when I have hundreds on items to link. I want a dynamic formula using with a combination of VLookup or Match where I would be able to drag the formula across.

I have attached the Excel file as an example of what I'm trying to achieve. Your help is much appreciated.

Regards

e_lad

Hi,

I am doing a vlookup to a different worksheet to return a value based on a unique key. However I now want to expand the vlookup to do the following - if the original vlookup returns a blank or a #N/A I want the formula to do a second vloopup to a different column in a different worksheet and return that value (if it exists).

Can anyone help?

How do you combine IF, LOOKUP and MATCH to come up with a formula in these conditions

If A7 is equal in any rows in sheet 2 column A, B6 is equal to any rows in sheet 2 column B, and A3 is equal to any rows in C, then return the value of sheet 2 column D.


Thanks!

Hi Folks,

I would like to perform a VLOOKUP operation & return values based on a match from bottom to top. By default, it would return the values corresponding to the top most match in the table array. I wanna do it the other way round.

I know there should be a way to do this. May be by using the LOOKUP function. But, nothing is striking me right now.

Regards,
Karan

We need to do a vlookup on an array of data where we combine two cells in a concatenation, but physically cannot make that concatenation in the sheet where the table array is pulling from, it will cause weeks of rework if we did. I need to make the table array look at the first two columns and concatenate them and then array the rest of the area I need, does this make sense?

Code:

=VLOOKUP(C$2,$C$5:$J$13,3,FALSE)

I need the table_array part of the formula above, $C$5:$J$5, to have the $C$5 part pull together C5 and D5 in a concatenation and then finish with :$J$3,etc)

We cannot got to the source and add a concatination column, really wish we could.

I have included a simple file to illustrate...

Thanks for any help

Hi,

When i vlookup a date, and if the cell has nothing in it, it = 00/01/1900, but i just want it to stay blank, if the vlookup = blanks.

How can i do the above please.

thank you in advance.