Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and IF
Hi,
I have 2 worksheets I need to build a VLOOKUP on worksheet 1 which will check a cell in worksheet 1 , check that it matches a cell in worksheet 2. Then run a VLOOKUP in sheet 1 to use against a seperate cell in sheet 2 Basically. I need the dates to match before the vlookup runs. I'm getting hopelessly bogged down. Can anyone help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and IF
Hi,
=VLOOKUP(A1,Sheet2!A1:B4,2) should do the job, provided : 1. the reference cell in located in sheet1 cell A1 2. the range to search is located in sheet2 cells A1:B4 3. the cell to be returned in sheet1 is located in column B of sheet2 HTH Cheers Carim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and IF
Hi,
First things first - what I think you are trying to do is return a value from sheet 1 into sheet 2 when a date criteria is matched. This means that your formula should be in sheet 2 and not in sheet 1. Second, I'm assuming that you want to return the value from sheet 1when both the account & date matches. If your data is as follows: Sheet 1: Column 1 = Date Column 2 = Account Column 3 = Amount Sheet 2: Column 1 = Date Column 2 = Account Column 3 = Formula to pick up amount from sheet 1 when the first 2 criteria are met A VLOOKUP function is only useful for matching a criteria and then returning a corresponding value. What you are trying to do is match 2 criteria (date & account). I would suggest the following: Insert a column 3 in sheet 1. Make this column a concatenation of columns 1 & 2 e.g. If A1 = 06/10/2006 & B1 = ABC Account Then insert a column in C with formula =A1&B1 to get "06/10/2006ABC Account" You now have a unique key. In sheet 2, insert another column C & enter the same formulas i.e. combine the date and account. The last step is then to input your formula in sheet 2. You want to match column C (date & account) with column c in sheet 1 (also date and account) & return the value. Formula becomes: VLOOKUP (Cell where the value you want to lookup is, Range in Sheet 1, column containing the information you want to return,false) E.g. VLOOKUP (C1, Sheet1! C1:D500, 2, false) - the false bit tells if there are no matches (produces an N/A! result). Hope this helps. "Dr Phibes" wrote: Hi, I have 2 worksheets I need to build a VLOOKUP on worksheet 1 which will check a cell in worksheet 1 , check that it matches a cell in worksheet 2. Then run a VLOOKUP in sheet 1 to use against a seperate cell in sheet 2 Basically. I need the dates to match before the vlookup runs. I'm getting hopelessly bogged down. Can anyone help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and IF
Hi, Thanks, but maybe I should have posted what I currently have. =IF((C12<'Workbook1'!A6),VLOOKUP(D12,'Workbook2'! $A:$D,3,FALSE)) There will be many of the same date (C12) but I need the V Lookup to make sure the date matches as there may be many of theaccount number (D12) Hope this makes things clearer |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and IF
Many thanks Boo..That certainly seems to be what I want to do.
Thanks for the help from both of you. regards "Boo" wrote: Hi, First things first - what I think you are trying to do is return a value from sheet 1 into sheet 2 when a date criteria is matched. This means that your formula should be in sheet 2 and not in sheet 1. Second, I'm assuming that you want to return the value from sheet 1when both the account & date matches. If your data is as follows: Sheet 1: Column 1 = Date Column 2 = Account Column 3 = Amount Sheet 2: Column 1 = Date Column 2 = Account Column 3 = Formula to pick up amount from sheet 1 when the first 2 criteria are met A VLOOKUP function is only useful for matching a criteria and then returning a corresponding value. What you are trying to do is match 2 criteria (date & account). I would suggest the following: Insert a column 3 in sheet 1. Make this column a concatenation of columns 1 & 2 e.g. If A1 = 06/10/2006 & B1 = ABC Account Then insert a column in C with formula =A1&B1 to get "06/10/2006ABC Account" You now have a unique key. In sheet 2, insert another column C & enter the same formulas i.e. combine the date and account. The last step is then to input your formula in sheet 2. You want to match column C (date & account) with column c in sheet 1 (also date and account) & return the value. Formula becomes: VLOOKUP (Cell where the value you want to lookup is, Range in Sheet 1, column containing the information you want to return,false) E.g. VLOOKUP (C1, Sheet1! C1:D500, 2, false) - the false bit tells if there are no matches (produces an N/A! result). Hope this helps. "Dr Phibes" wrote: Hi, I have 2 worksheets I need to build a VLOOKUP on worksheet 1 which will check a cell in worksheet 1 , check that it matches a cell in worksheet 2. Then run a VLOOKUP in sheet 1 to use against a seperate cell in sheet 2 Basically. I need the dates to match before the vlookup runs. I'm getting hopelessly bogged down. Can anyone help |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and IF
Ok, It's now working. Many thanks to both of you. regards Dr Phibes |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and IF
By combining the date and account to create a unique key you should be able
to preform your lookup on this key - you mention a matching of the date and then performing a lookup once the date matches. What I was suggesting factors into account that you will have many dates/many accounts i.e. you will have account ABC for 5/10/06, ABC for 6/10/06 and so on. You will also have account DEF for 5/10/06, DEF for 6/10/06 and so on (your data workbook). By adding in this extra column (combining date and account), you can look for a match in your output workbook/worksheet on date & account i.e. where both date and account match, return the value. This has limitations - if there are several instances meeting the criteria, Excel will return the first match e.g. you may have a balance for account ABC on 5/10/06 and may have another balance further down the list for account ABC for 5/10/06(this may not happen but thought that I'd mention it). Not knowing what is in each of your cells I have to take a guess here, but, this is what your formula is doing: =IF((C12<'Workbook1'!A6),VLOOKUP(D12,'Workbook2'! $A:$D,3,FALSE)) If the value in cell C12 is NOT equal to the value in cell A6 in workbook1 then perform a lookup on the value held in cell d12 (looking at columns A to D in Workbook 2, returning the value in the 3rd column or else returning an error). You said that you wanted to match a date and account - I'm not sure how checking if cell C12 is not equal to cell A6 in workbook 1 achieves this??? I appreciate that cell D12 probably contains your account number, column A:D on workbook 2 contain dates/accounts/values so your lookup formula is correct in that it will return the value in column C when the account number is matched (this could be the date - I'm guessing) - I think it is the first part of your if statement that's got to be causing you to get the wrong (or no) result. Does this help? "Dr Phibes" wrote: Hi, Thanks, but maybe I should have posted what I currently have. =IF((C12<'Workbook1'!A6),VLOOKUP(D12,'Workbook2'! $A:$D,3,FALSE)) There will be many of the same date (C12) but I need the V Lookup to make sure the date matches as there may be many of theaccount number (D12) Hope this makes things clearer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |