Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If(And; Vlookup
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If(And; Vlookup
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If(And; Vlookup
Thanks for the feed back.
"TomPl" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |