Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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: 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 ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531278 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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 "albertmb" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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())) albertmb wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |