VLOOKUP

[Guest]

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

 

[Bryan Hessey]

Excel Help describes the item as:

Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_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

---

 

[Guest]

If you want both transactions - i assume you mean 100+300, then use the
SUMPRODUCT function:

=SUMPRODUCT(--(B1:B300="John"),(C1:C300))


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

where A1 contains "John2 (no "s required)

This will look through array B1 to B300 and every time finds "John" 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

 

[Dave Peterson]

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:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))