A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VLOOKUP Question



 
 
Thread Tools Display Modes
  #1  
Old October 13th, 2003, 03:34 PM
Jeff Armstrong
external usenet poster
 
Posts: n/a
Default VLOOKUP Question

I have a VLOOKUP function that retrieves information from
a list. There will be times that the information trying
to be retrieved won't be in the list. This generates a
#NA for the cell. My question is how can I leave this
cell blank if no data is found? Below is my formula.

=VLOOKUP(A2,$B:$X,14,FALSE)

Thanks,
Jeff
  #2  
Old October 13th, 2003, 03:42 PM
Peo Sjoblom
external usenet poster
 
Posts: n/a
Default VLOOKUP Question

One way


=IF(COUNTIF($B:$B,A2),VLOOKUP(A2,$B:$X,14,FALSE)," ")



--

Regards,

Peo Sjoblom


"Jeff Armstrong" wrote in message
...
I have a VLOOKUP function that retrieves information from
a list. There will be times that the information trying
to be retrieved won't be in the list. This generates a
#NA for the cell. My question is how can I leave this
cell blank if no data is found? Below is my formula.

=VLOOKUP(A2,$B:$X,14,FALSE)

Thanks,
Jeff



  #3  
Old October 13th, 2003, 06:57 PM
Felipe
external usenet poster
 
Posts: n/a
Default VLOOKUP Question

=IF(ISNA(VLOOKUP(A2,$B:$X,14,FALSE)),"",VLOOKUP
(A2,$B:$X,14,FALSE))

-----Original Message-----
I have a VLOOKUP function that retrieves information from
a list. There will be times that the information trying
to be retrieved won't be in the list. This generates a
#NA for the cell. My question is how can I leave this
cell blank if no data is found? Below is my formula.

=VLOOKUP(A2,$B:$X,14,FALSE)

Thanks,
Jeff
.

  #4  
Old December 5th, 2003, 09:12 PM
Jon Barchenger[MS]
external usenet poster
 
Posts: n/a
Default vlookup question

Good afternoon -

You can use the Column() function to replace the column index number.
In this case you would need to use column()-1 in place of the column index number,
if I understand your layout.

=vlookup(A2,A25:M25,2) would be

=vlookup(A2,A25:M25,column()-1) if this formula is in column C.


Please let me know if you need further help with this issue.

Thanks,
Jon Barchenger




--------------------
**Thread-Topic: vlookup question
**thread-index: AcO7SmqbUiRzx14URyCgvnS3nTjv/A==
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**From: "=?Utf-8?B?anVkeQ==?="
**Subject: vlookup question
**Date: Fri, 5 Dec 2003 08:11:18 -0800
**Lines: 8
**Message-ID:
**MIME-Version: 1.0
**Content-Type: text/plain;
** charset="Utf-8"
**Content-Transfer-Encoding: 7bit
**X-Newsreader: Microsoft CDO for Windows 2000
**Content-Class: urn:content-classes:message
**Importance: normal
**Priority: normal
**X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
**Newsgroups: microsoft.public.excel.worksheet.functions
**NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
**Path: cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl
**Xref: cpmsftngxa07.phx.gbl microsoft.public.excel.worksheet.functions:175179
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**Is there a way to copy a vlookup formula to the next column and have the col_index_num adjust automatically?



example of original vlookup formula is in cell C2, looking up the value in cell A2, table-array is A25:M25 and is using col_index 2

=vlookup(A2,A25:M25,2)



when I copy the formula to cell D2, I want it to use the same lookup value and table-array, but col_index 3



I want to copy the formula like this 12 times and don't want to have to edit the col_index number each time I copy it - it there a way to make the col_index
adjust automatically?
**


  #5  
Old March 10th, 2004, 12:40 PM
Andy B
external usenet poster
 
Posts: n/a
Default VLOOKUP question

Craig

I think it's a limitation of the length of a formula that Excel allows. When
the file is closed, the formula becomes too long, because it has to include
the path of the file. This means that the formula is truncated. When the
file is open, it doesn't.

I read a good explanation of this a few days ago but I can't find it now!!

Andy.

"Craig H" wrote in message
news
Hi folks,

I've been sent 2 workbooks lets call them Week 1 and Week 2. Both
workbooks have the same structure etc with only some of the values changed.

On one of the sheets in each workbook there is a cell (P31) which contains

the formula

=IF(ISNA(VLOOKUP(C31,'C:\Agency Average\Period 73\[Period 73 Agency

Average.xls]Agency Average Bonus'!$A$1:$R$980, WEEK, FALSE)),0,
VLOOKUP(C31,'C:\Agency Average\Period 73\[Period 73 Agency
Average.xls]Agency Average Bonus'!$A$1:$R$980, WEEK, FALSE))

Now if i open 'Week 1' and tell it NOT to update links (as i don't have

the file mentioned in the formula above) then the cell P31 displays £35. If
i close all this down and then open 'Week 2' (again not updating links) then
cell P31 in this workbook returns £190. At this stage everything is fine and
the values are correct.

However, if i leave 'Week 1' open and then open 'Week 2' the cell P31

returns £190 in BOTH workbooks !!!! If i have the 'Week 2' open showing £190
and then open 'Week 1' which has £35 then BOTH show £35 !!!!!!

Does anyone know what is happening ? Does Excel store this lookup value

somewhere which then gets mixed up ?

Any help would be greatly appreciated.


TIA

Craig.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.