|
VLookUp with Dates
I have a long list that I'm comparing to a short list; I need to pull dates
from other sheets in the same workbook to the one I'm working on. My current formula is: =IF(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) My problem is this: If an item is on the sheet I'm working on, but not on the 'Billing 01-05' sheet, I get a blank cell. Ok. However, if the item is on both lists but blank on the 'Billing 01-05' list, it returns the value of 1/0. I did check, and the value in the blank column really is blank (no hidden characters), and the dates are in the same format on both sheets (mm/yy). Thanks in advance for your help!! |
I am assuming your format is month day. It is pulling back the blank as a
zero. try =IF(or(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)=""),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) "o1darcie1o" wrote: I have a long list that I'm comparing to a short list; I need to pull dates from other sheets in the same workbook to the one I'm working on. My current formula is: =IF(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) My problem is this: If an item is on the sheet I'm working on, but not on the 'Billing 01-05' sheet, I get a blank cell. Ok. However, if the item is on both lists but blank on the 'Billing 01-05' list, it returns the value of 1/0. I did check, and the value in the blank column really is blank (no hidden characters), and the dates are in the same format on both sheets (mm/yy). Thanks in advance for your help!! |
That formula exactly changed the 1/0 to blank, but the #N/A's came back!
--Darc "bj" wrote: I am assuming your format is month day. It is pulling back the blank as a zero. try =IF(or(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)=""),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) "o1darcie1o" wrote: I have a long list that I'm comparing to a short list; I need to pull dates from other sheets in the same workbook to the one I'm working on. My current formula is: =IF(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) My problem is this: If an item is on the sheet I'm working on, but not on the 'Billing 01-05' sheet, I get a blank cell. Ok. However, if the item is on both lists but blank on the 'Billing 01-05' list, it returns the value of 1/0. I did check, and the value in the blank column really is blank (no hidden characters), and the dates are in the same format on both sheets (mm/yy). Thanks in advance for your help!! |
I don't understand why the #NA came back but then try
=IF(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),"",if(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)="","",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0))) "o1darcie1o" wrote: That formula exactly changed the 1/0 to blank, but the #N/A's came back! --Darc "bj" wrote: I am assuming your format is month day. It is pulling back the blank as a zero. try =IF(or(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)=""),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) "o1darcie1o" wrote: I have a long list that I'm comparing to a short list; I need to pull dates from other sheets in the same workbook to the one I'm working on. My current formula is: =IF(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) My problem is this: If an item is on the sheet I'm working on, but not on the 'Billing 01-05' sheet, I get a blank cell. Ok. However, if the item is on both lists but blank on the 'Billing 01-05' list, it returns the value of 1/0. I did check, and the value in the blank column really is blank (no hidden characters), and the dates are in the same format on both sheets (mm/yy). Thanks in advance for your help!! |
That worked exactly!! Thank you so much!!!
"bj" wrote: I don't understand why the #NA came back but then try =IF(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),"",if(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)="","",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0))) "o1darcie1o" wrote: That formula exactly changed the 1/0 to blank, but the #N/A's came back! --Darc "bj" wrote: I am assuming your format is month day. It is pulling back the blank as a zero. try =IF(or(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)=""),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) "o1darcie1o" wrote: I have a long list that I'm comparing to a short list; I need to pull dates from other sheets in the same workbook to the one I'm working on. My current formula is: =IF(ISNA(VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)),"",VLOOKUP(C:C,'Billing 01-05'!C:E,3,0)) My problem is this: If an item is on the sheet I'm working on, but not on the 'Billing 01-05' sheet, I get a blank cell. Ok. However, if the item is on both lists but blank on the 'Billing 01-05' list, it returns the value of 1/0. I did check, and the value in the blank column really is blank (no hidden characters), and the dates are in the same format on both sheets (mm/yy). Thanks in advance for your help!! |
| All times are GMT +1. The time now is 05:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com