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

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

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

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

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

VLOOKUP and IF
 

Ok, It's now working.

Many thanks to both of you.

regards

Dr Phibes

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