Easter Function help..

Posted by Ben Houck on April 15, 2001 10:05 AM

I need help to pull data from the following format:

101 1 EXI 40 470216.57
101 1 EXO 94 -37547.28
101 1 LIQ 3841 -1474567.97
101 1 PUR 944 1434700.36
101 2 EXI 16 46840.7
101 2 EXO 13 -10381.98
101 2 LIQ 14 -32684.9
101 2 PUR 19 19620

I need a function that will verify the first three columns of information and then return the fifth into another spreadsheet. Dave helped me with a If.And.Vlookup but it only vefified them and then returned false after reading the third column.

I need one that will scan all three columns and only return false after reading all the rows within the spreadsheet. I'm sure this sounds confusing so e-mail if you could use some clarfication. Listed below is the if statement I tried before.

=IF(AND(VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,2,FALSE)=1,VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,3,FALSE)="PUR"),VLOOKUP($B7,'[TranByFund03-01.xls]TranByFund2001'!$B$1:$F$1000,5,FALSE),0)

Thanks
Ben

Posted by Aladin Akyurek on April 15, 2001 11:49 AM

Ben

Lets try to simplify things a bit.

1) Go to the range $B$1:$F$1000 and insert a column before column E. The range is now B1:G1000. Enter the following formula in E1

=B1&C1&D1

and copy this down as far as needed.

2) Select the range E1:E1000 and name the selected range FUNDIDS. Select also the range E1:G1000 and this range FUNDDATA.

2) On the sheet into which you want to retrieve data, $B7 contains (I assume) one of {101,102,...}. I'd suggest to place the other criteria (that is, 1 and PUR) also in cells of their own, say in A1 and A2.

Now try the following formula:

=IF(ISNUMBER(MATCH($B7&$A$1&$A$2,TranByFund03-01.xls !FUNDIDS,0)),VLOOKUP($B7&$A$1&$A$2,TranByFund03-01.xls !FUNDDATA,3,0),"")

Aladin

[ With thanks to Tim, the owner of the original idea, who is naturally not responsible for mistakes here if any. ]

-------------------------------------

Posted by BenHouck on April 16, 2001 7:52 AM

This formula works perfectly. You guys are totally class.

Thanks again
Ben