If equation with a VLookUp


L

LiziC

Hello,
I'm wondering if anyone can help me? I'm trying to use a combination o
an If equation and a VLookup across two different spreadsheets and I'
not sure how to word the formula.

Basically, on the SZX TEST 1.0 spreadsheet, if the contents of Column
is 'Y' then on the Column B on the SZX Delivery Note 1.0 is filled i
with the VLookup, and if Column C is blank, then Column B is als
blank.

The formula I've been trying is:
=IF('[SZX TEST 1.0.xls]MT'!$C:$C="Y","=VLOOKUP($A:$A,'[SZX SHENZEN TES
(2).xls]MT'!$B:$AA,6,FALSE)","")

And I know the VLookUp part is right but I think I've messed up o
wording of it perhaps? To be honest Column C doesn't need to be 'Y', i
could be a number like '1' just as long as the formula works I'm no
fussed on letter/number is in Column C.

I realise I probably haven't explained it too well, but I've attache
the two spreadsheets if that helps?

Any help would be greatly appreciated!!

Many thanks,
Li

+-------------------------------------------------------------------
|Filename: SZX TEST 1.0.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=359
|Filename: SZX Delivery Note 1.0.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=360
+-------------------------------------------------------------------
 
Ad

Advertisements

D

Don Guillett

Hello,
I'm wondering if anyone can help me? I'm trying to use a combination of
an If equation and a VLookup across two different spreadsheets and I'm
not sure how to word the formula.

Basically, on the SZX TEST 1.0 spreadsheet, if the contents of Column C
is 'Y' then on the Column B on the SZX Delivery Note 1.0 is filled in
with the VLookup, and if Column C is blank, then Column B is also
blank.

The formula I've been trying is:
=IF('[SZX TEST 1.0.xls]MT'!$C:$C="Y","=VLOOKUP($A:$A,'[SZX SHENZEN TEST
(2).xls]MT'!$B:$AA,6,FALSE)","")

And I know the VLookUp part is right but I think I've messed up on
wording of it perhaps? To be honest Column C doesn't need to be 'Y', it
could be a number like '1' just as long as the formula works I'm not
fussed on letter/number is in Column C.

I realise I probably haven't explained it too well, but I've attached
the two spreadsheets if that helps?

Any help would be greatly appreciated!!

Many thanks,
Liz


+-------------------------------------------------------------------+
|Filename: SZX TEST 1.0.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=359|
|Filename: SZX Delivery Note 1.0.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=360|
+-------------------------------------------------------------------+
This tested in the source file. So,add your filename and change b4 to a25
=OFFSET($G$1,SUMPRODUCT(--($B$4:$B$40=B4)*--(($C$4:$C$40)="Y")*--(ROW($G$4:$G$40)-1)),0)
 
L

LiziC

'Don Guillett[_2_ said:
;1601424']On Wednesday, May 2, 2012 1:55:27 PM UTC-5, LiziC wrote:-
Hello,
I'm wondering if anyone can help me? I'm trying to use a combinatio of
an If equation and a VLookup across two different spreadsheets an I'm
not sure how to word the formula.

Basically, on the SZX TEST 1.0 spreadsheet, if the contents of Colum C
is 'Y' then on the Column B on the SZX Delivery Note 1.0 is filled in
with the VLookup, and if Column C is blank, then Column B is also
blank.

The formula I've been trying is:
=IF('[SZX TEST 1.0.xls]MT'!$C:$C="Y","=VLOOKUP($A:$A,'[SZX SHENZE TEST
(2).xls]MT'!$B:$AA,6,FALSE)","")

And I know the VLookUp part is right but I think I've messed up on
wording of it perhaps? To be honest Column C doesn't need to be 'Y' it
could be a number like '1' just as long as the formula works I'm not
fussed on letter/number is in Column C.

I realise I probably haven't explained it too well, but I've attached
the two spreadsheets if that helps?

Any help would be greatly appreciated!!

Many thanks,
Liz


+-------------------------------------------------------------------+
|Filename: SZX TEST 1.0.zip |
|Download http://www.excelbanter.com/attachment.php?attachmentid=359|
|Filename: SZX Delivery Note 1.0.zip |
|Download http://www.excelbanter.com/attachment.php?attachmentid=360|
+-------------------------------------------------------------------+
This tested in the source file. So,add your filename and change b4 t
a25
=OFFSET($G$1,SUMPRODUCT(--($B$4:$B$40=B4)*--(($C$4:$C$40)="Y")*--(ROW($G$4:$G$40)-1)),0)

Hi Don,

I can't get this Offset formula to work in my spreadsheet, where exactl
does the filename need to go in the formula?

Thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
Ad

Advertisements

L

LiziC

Mazzaropi;1601498 said:
Dear LiziC, Good Afternoon.

Save your worksheet at any free site, e.g. www.4shared.com and put th
link here.

Hi Mazzaropi,

Here are the links to the files:

SZX
http://www52.zippyshare.com/v/43984583/file.html

DN MT (If /VLookUp)
http://www52.zippyshare.com/v/12653953/file.html

DN MT (Offset)
http://www52.zippyshare.com/v/96609640/file.html

Many thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top