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. |
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 |
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 |
All times are GMT +1. The time now is 02:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com