Hi,
I'm trying to run a calculation with numbers in column B based on a VLOOKUP result in column A.
E.g. =IF(ISERROR(VLOOKUP($A5,$A$1:$A$3,1,FALSE)),"",B5-B2)
This will find a match for A5 (Sausage) within the range of A1-A3, if no match the cell is left blank. So far so good.
The substract part is to illustrate what I'm trying to do. B column contains my numbers I want to substract. In this case, if A5 = Sausage and A2 = Sausage my calculation would be correct (-5), but if Sausage had been in A1 the calcuation would be wrong due to the B5-B2 defining the cells.
I thought the "1" or [1,2,3 etc.}, column index, defines where the number(s) I want to use can be found, but how do I tell Excel what i want to do with the numbers? How do I get around not defining the cells but rather let it be based on the VLOOKUP result?... Also played around with =SUM(VLOOKUP... but couldn't get that to work either, so need your help.
I'm trying to run a calculation with numbers in column B based on a VLOOKUP result in column A.
E.g. =IF(ISERROR(VLOOKUP($A5,$A$1:$A$3,1,FALSE)),"",B5-B2)
This will find a match for A5 (Sausage) within the range of A1-A3, if no match the cell is left blank. So far so good.
The substract part is to illustrate what I'm trying to do. B column contains my numbers I want to substract. In this case, if A5 = Sausage and A2 = Sausage my calculation would be correct (-5), but if Sausage had been in A1 the calcuation would be wrong due to the B5-B2 defining the cells.
I thought the "1" or [1,2,3 etc.}, column index, defines where the number(s) I want to use can be found, but how do I tell Excel what i want to do with the numbers? How do I get around not defining the cells but rather let it be based on the VLOOKUP result?... Also played around with =SUM(VLOOKUP... but couldn't get that to work either, so need your help.
| Apple | 5 | Result in this column |
| Sausage | 8 | |
| Beer | 7 | |
| Sausage | 3 | -5 |
| Beer | 1 | |
| Apple | 9 | |
