VLOOKUP and IF

[Guest]

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

 

[Carim]

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

 

[Guest]

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! C1500, 2, false) - the false bit tells if there are
no matches (produces an N/A! result).

Hope this helps.

 

[Guest]

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

 

[Guest]

Many thanks Boo..That certainly seems to be what I want to do.

Thanks for the help from both of you.


regards

 

[Guest]

Ok, It's now working.

Many thanks to both of you.

regards

Dr Phibes

 

[Guest]

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 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?