If(And; Vlookup

[HDuncan]

I am trying to look up a value in a table that if two variables are met.
If Col F says "Receipt" and Col G has "49" then lookup Col F:O and return
the value in the 7 col over.

=IF(AND(Sheet1!F:F='Productivity Files'!A7,Sheet1!G:G='Productivity
Files'!BB3),VLOOKUP(A7,Transactions,7,0)) - This returns FALSE rather than
the actual number.

=SUMPRODUCT((Sheet1!F:F="Receipt"),(Sheet1!G:G="49"),(Sheet1!L:L)) - This
returns #NUM! rather than the actual number. Not all variables are numbers.

 

[TomPl]

You have several problems with what you are trying to do. SumProduct will
add all occurances when the criteria are met, so either you have only one
occurance or you get different answers. Also, SumProduct will not return
text.

Vlookup will not analyse more than one criteria.

I think the easiest way to do what you want would be to create a helper
column in you lookup data. I column E put the formula =concatenate(F2 & G2)
[assuming this formula is on row 2] then copy it down to the bottom of the
data field. That should returm "Receipt49" if F2 is "Receipt" and G2 is "49".

Then your lookup formula would be =vlookup("Receipt49",E:O,8,false). This
will give you the result of the first occurance of your criteria in the data
field.

I hope this helps.

Tom

 

[HDuncan]

Thanks for the feed back.