formula Help needed

[MeAgain]

Hi
I have two worksheets one is purchases and another is named Bank
on purchases I have 4 columns

:::::A::::::::::::::::::::::::::B:::::::::::::::::::::::::::C:
1::::: WHSmith
2:::ATE----------Dr------------Cr
3::::2-10----------£480.18------(Formula needed)

Then I have Bank
DATE----------Details---------Dr------------ Cr
9-10-----------WHSmith------ 0-----------£480.18

What can I put in C3 to lookup for £480.18 in Bank worksheet and return the
value in C3?????
TIA
hope I explained it clearly..

 

[mdijulio]

Do you just want whatever is in the one cell to appear in another cell?

If you do, just go to the cell where you want the information to appear
and type the equals sign = and then click on the cell where the
information currently resides. You're basically saying: "Whatever's
in C1, also put here in C2.

 

[MeAgain]

I know what you are saying but it was just an example.
I have more 1000 rows in Bank Sheet. I update it every days from the Bank
website and I have different supplier accounts. WHSmith is just one of them.
I want to lookup for £480.18 in range Bank!$C$1:$D$1000
I did try VLOOKUP but it won't work.
I am looking for any formula that would return the Extact Matched value from
the range.

 

[Katherine Coombs]

Hi there,

It sounds as though you want an "IF" function, but I'm not sure what your
criteria for a match would be but I'm guessing that it is looking for the
contents of B1 (in the purchases worksheet) in the B column of the Bank
worksheet, does this sound right?

I know what you are saying but it was just an example.
I have more 1000 rows in Bank Sheet. I update it every days from the Bank
website and I have different supplier accounts. WHSmith is just one of them.
I want to lookup for £480.18 in range Bank!$C$1:$D$1000
I did try VLOOKUP but it won't work.
I am looking for any formula that would return the Extact Matched value from
the range.

 

[Max]

Here's an approach using OFFSET and MATCH (for left lookup)
which you might want to play around with...

In the "purchases" sheet, let's say you have redesigned
the table structure below (this is suggested, btw) in cols A - E,
with data from row 2 downwards, for e.g.:

Org .....Date.... .....Dr.Cr.....Date
WHSmith ..02-Oct-03.. £480.18.(amt?).....(date of Cr?)
MPH ....18-Oct-03.. ..£50.00.(amt?)......(date of Cr?)
etc

And you would like to extract the info for
cols D & E from the "Bank" sheet

In the "Bank" sheet you have 4 cols A - D,
with data from row 2 downwards, for e.g.:

Date ...........Details.... ...Dr........ Cr
09-Oct-03...WHSmith....0 ...£480.18
15-Oct-03 ..MPH............0 .....£50.00
etc

Set-up steps:

In the "Bank" sheet

Put in E2: =TRIM(B2&"_"&D2)
copy down

In the "purchases" sheet

Put in D2:
=OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-1,1,1)

Format D2 as currency (£)

Put in E2:
=OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-4,1,1)

Format E2 as date (dd-mmm-yy)

Select D2:E2 and copy down cols D:E

Using the sample data above, the values returned in
the "purchases sheet" cols D:E would be as shown:

Org .....Date.... .....Dr...Cr..Date
WHSmith ..02-Oct-03.. £480.18.£480.18.....09-Oct-03
MPH ....18-Oct-03.. ..£50.00...£50.00.....15-Oct-03

Hope the above helps

Max

 

[MeAgain]

thanks it will work for me.

Here's an approach using OFFSET and MATCH (for left lookup)
which you might want to play around with...

In the "purchases" sheet, let's say you have redesigned
the table structure below (this is suggested, btw) in cols A - E,
with data from row 2 downwards, for e.g.:

Org .....Date.... .....Dr.Cr.....Date
WHSmith ..02-Oct-03.. £480.18.(amt?).....(date of Cr?)
MPH ....18-Oct-03.. ..£50.00.(amt?)......(date of Cr?)
etc

And you would like to extract the info for
cols D & E from the "Bank" sheet

In the "Bank" sheet you have 4 cols A - D,
with data from row 2 downwards, for e.g.:

Date ...........Details.... ...Dr........ Cr
09-Oct-03...WHSmith....0 ...£480.18
15-Oct-03 ..MPH............0 .....£50.00
etc

Set-up steps:

In the "Bank" sheet

Put in E2: =TRIM(B2&"_"&D2)
copy down

In the "purchases" sheet

Put in D2:
=OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-1,1,1)

Format D2 as currency (£)

Put in E2:
=OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-4,1,1)

Format E2 as date (dd-mmm-yy)

Select D2:E2 and copy down cols D:E

Using the sample data above, the values returned in
the "purchases sheet" cols D:E would be as shown:

Org .....Date.... .....Dr...Cr..Date
WHSmith ..02-Oct-03.. £480.18.£480.18.....09-Oct-03
MPH ....18-Oct-03.. ..£50.00...£50.00.....15-Oct-03

Hope the above helps

Max

 

[Max]

you're welcome !