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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|