Using function in worksheet

  • Thread starter Thread starter Louie
  • Start date Start date
L

Louie

Hi All,

Bank A/C no. Holder Cash In Cash Out Net Total
----------------- --------- ----------- ------------ ------------
1234 Father 1200 450 750
1234 Mother 800 125 675
1234 Son a 15000 885 14115
1234 Son b 1200 74
1126
1234 Daughter a 250 155 95
5678 Mother 1200 45 1155
5678 Daughter b 850 125 725
8889 Father 25000 1200 23800
8889 Son a 4500 1256 3244
8889 Daughter b 7800 2500 5300



I am new in using Excel and are now in baby class.



I have a simple DATA table as above in sheet2. When doing some sort of data
extraction in sheet1 using VLOOKUP function on the criterion of ¡§ Bank A/C
no¡¨ say ¡§ 1234 ¡¨. I get only those data of FATHER but not all the family
members under the same Bank A/C no.



What should I do to get what I want, if vlookup is not the suitable function
to use, kindly suggest your advice PLEASE.

Many thank
 
It depends what you're trying to do with the data. VLOOKUP only finds the
first entry in the list.

Perhaps more useful to you would be the SUMIF function, which would enable
you to sum only those entries of a column that correspond to the bank account
you're after.

A more generally useful way of doing this would be to use a formula like this:
=SUMPRODUCT(--(A2:A100=1234),(C2:C100)), which would give you the same
result but is scaleable to adding more conditions.

This was talked about a week or so ago on this forum if you're looking for
more details.

Best

Glenton
www.leviqqio.com
 
Louie,

Not sure if it's quite what you want, but I would suggest using the
"Autofilter" feature. Data>Filter>Autofilter. This makes each column able to
be filtered, so you could choose just one A/c number, or all the Fathers, or
any combination, including using wildcards.

Regards,

Rob
 
Hi, Rob

Thank for your response. "Autofilter" is was I am using now.
I just wonder if the data is keeps in sheet_2, how can I get the same result
in sheet_1 as using AUTOFILTER in sheet_2. Your further advice is deeply
appreciated. Thank !

With Regard

Louie
 
Back
Top