Andrew has attended:
Excel Advanced course
Vlookup/sumif
I am trying to create a fomulae to look in a range find all rows with a particular date and pull back all amounts that match. This I can do. However I cant add another variable which limits the return to only those fulfilling another criteria i.e a set bank a/c.
How can I nest a vlookup and if/sumif formulae?
RE: vlookup/sumif
Hi Andrew,
Thank you for the question. You are not able to nest the Vlookup and the if/sumif formulae, however, how about looking into the possibility of putting the AND and OR functions into the IF function??
I will give you an example
=IF(OR(G8="NA", "H8="No"), "No bonus", F8*1.5%))
so you would be in the detination cell and if either of the cells are NA or No the cell will return No bonus, if that is not the case it will return whatever F8*1.5% is.
Alternatively you could try
=IF(AND(H8="Yes", F8>12000), "Yes", "")
and the standard IF conditions apply.
See if you can get around it that way
Tracy
RE: vlookup/sumif
How would I get this work over a range.so if you wanted all Yes's in Col A. All 04/06/07 in Col B and the relevant amount in Col C?
RE: vlookup/sumif
=IF(AND(SUM(A3:A5)=20,B3>20/2/75),"Result","Not applicable") You can use the first argument as a range if you like.
Tracy
