If(And; Vlookup

H

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup and If statement help 1
If( AND( with VLOOKUP.... 4
Nested If Statement help 1
Vlookup with 2 Conditions 2
sum and if 4
IF STATEMENTS COMBINED WITH VLOOKUPS 2
IF and OR Function 3
if statement 2

Top