software

Hi everyone,
I was given a formula from this group regarding VLOOKUP, extreemly helpfull
and many thanks, but I do not like to use formulas I do not understand. Can
anyone please explain to me how it works. The first part of the formula I
understand it is the second part I do not understand:
=VLOOKUP(A1,B1:C300,2,FALSE). What do the number 2 and False represent in
the formula?

I use it to find transactions related to names, but if I have transactions
relating to the same name it always goes for the first transaction: Example:

B1= John - C1= 100
B2= Peter - C2= 200
B3= John - C3= 300

If I want the result in E1, when I get the name John it will always give me
the first result, is there a possibilty to get both transactions?

Thank you
Albert


Excel Help describes the item as:

Syntax
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

where Col_Index is the second column of your lookup table (ie, column
C), and 'False' is described as:

If range_lookup is TRUE, the values in the first column of table_array
must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z,
FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If
range_lookup is FALSE, table_array does not need to be sorted.

Where False is used a match must be found or the item will return a
#N/A

---

albertmb Wrote:
gt; Hi everyone,
gt; I was given a formula from this group regarding VLOOKUP, extreemly
gt; helpfull
gt; and many thanks, but I do not like to use formulas I do not understand.
gt; Can
gt; anyone please explain to me how it works. The first part of the
gt; formula I
gt; understand it is the second part I do not understand:
gt; =VLOOKUP(A1,B1:C300,2,FALSE). What do the number 2 and False represent
gt; in
gt; the formula?
gt;
gt; I use it to find transactions related to names, but if I have
gt; transactions
gt; relating to the same name it always goes for the first transaction:
gt; Example:
gt;
gt; B1= John - C1= 100
gt; B2= Peter - C2= 200
gt; B3= John - C3= 300
gt;
gt; If I want the result in E1, when I get the name John it will always
gt; give me
gt; the first result, is there a possibilty to get both transactions?
gt;
gt; Thank you
gt; Albert--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=531278If you want both transactions - i assume you mean 100 300, then use the
SUMPRODUCT function:

=SUMPRODUCT(--(B1:B300=quot;Johnquot;),(C1:C300))=SUMPRODUCT(--(B1:B300=A1),(C1:C300))

where A1 contains quot;John2 (no quot;s required)

This will look through array B1 to B300 and every time finds quot;Johnquot; set a
TRUE value which is converted to a number (1) by the -- in front of the
brackets; FALSE has value 0. Each value is multiplied by the corresponding C
value and the array summated.

So if data was only in the 3 cells shown in your example you will get:

{1,0,1} * {100,200,300} to give 1*100 0*200 1*300 =400

HTH

quot;albertmbquot; wrote:

gt; Hi everyone,
gt; I was given a formula from this group regarding VLOOKUP, extreemly helpfull
gt; and many thanks, but I do not like to use formulas I do not understand. Can
gt; anyone please explain to me how it works. The first part of the formula I
gt; understand it is the second part I do not understand:
gt; =VLOOKUP(A1,B1:C300,2,FALSE). What do the number 2 and False represent in
gt; the formula?
gt;
gt; I use it to find transactions related to names, but if I have transactions
gt; relating to the same name it always goes for the first transaction: Example:
gt;
gt; B1= John - C1= 100
gt; B2= Peter - C2= 200
gt; B3= John - C3= 300
gt;
gt; If I want the result in E1, when I get the name John it will always give me
gt; the first result, is there a possibilty to get both transactions?
gt;
gt; Thank you
gt; Albert

If you're looking for a sum of all transactions, you could use =sumif().

If you want a count of all transactions, maybe =countif()

You may want to read Debra Dalgleish's notes:
www.contextures.com/xlFunctions02.html (for =vlookup())
and
www.contextures.com/xlFunctions03.html (for =index(match()))

albertmb wrote:
gt;
gt; Hi everyone,
gt; I was given a formula from this group regarding VLOOKUP, extreemly helpfull
gt; and many thanks, but I do not like to use formulas I do not understand. Can
gt; anyone please explain to me how it works. The first part of the formula I
gt; understand it is the second part I do not understand:
gt; =VLOOKUP(A1,B1:C300,2,FALSE). What do the number 2 and False represent in
gt; the formula?
gt;
gt; I use it to find transactions related to names, but if I have transactions
gt; relating to the same name it always goes for the first transaction: Example:
gt;
gt; B1= John - C1= 100
gt; B2= Peter - C2= 200
gt; B3= John - C3= 300
gt;
gt; If I want the result in E1, when I get the name John it will always give me
gt; the first result, is there a possibilty to get both transactions?
gt;
gt; Thank you
gt; Albert

--

Dave Peterson