If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup
A B C E F
1 SKU SUM SKU SUM 2 85 10 85 10 3 86 20 86 20 4 87 30 88 30 5 89 40 I'm trying to make a formula that can tell me the sum of each number. Like her, that 85 picks out that it's 10 and so on. But at 88, which isn't in the original list, it just takes the number above. I wish it could come up as an error, when it doesn't exist. The formula I have used here is: =VLOOKUP(D;A:B;2) |
#2
|
|||
|
|||
Lookup
Change your formula to this:
=VLOOKUP(D2;A:B;2;0) and copy down. The 0 at the end of the VLOOKUP forces it to look for exact matches, so this returns #N/A if the number is not present. Hope this helps. Pete On Apr 23, 1:05*pm, Kaisa wrote: * * * * A * * * B * * * C * * * E * * * F 1 * * * SKU * * SUM * * * * * * SKU * * SUM 2 * * * 85 * * *10 * * * * * * *85 * * *10 3 * * * 86 * * *20 * * * * * * *86 * * *20 4 * * * 87 * * *30 * * * * * * *88 * * *30 5 * * * 89 * * *40 * * * * * * * * * * * I'm trying to make a formula that can tell me the sum of each number. Like her, that 85 picks out that it's 10 and so on. But at 88, which isn't in the original list, it just takes the number above. I wish it could come up as an error, when it doesn't exist. The formula I have used here is: =VLOOKUP(D;A:B;2) |
#3
|
|||
|
|||
Lookup
Check out...
=LOOKUP(D2,A:A,B:B) -- Jacob (MVP - Excel) "Kaisa" wrote: A B C E F 1 SKU SUM SKU SUM 2 85 10 85 10 3 86 20 86 20 4 87 30 88 30 5 89 40 I'm trying to make a formula that can tell me the sum of each number. Like her, that 85 picks out that it's 10 and so on. But at 88, which isn't in the original list, it just takes the number above. I wish it could come up as an error, when it doesn't exist. The formula I have used here is: =VLOOKUP(D;A:B;2) |
#4
|
|||
|
|||
Lookup
Oops....I misread your post
Check out help on VLOOKUP(). In the below formula the last criteria is whether to look for an exact match or not... =VLOOKUP(D2,A:B,2,0) To ignore this error and replace with blank =IF(ISNA(VLOOKUP(D2,A:B,2,0)),"",VLOOKUP(D2,A:B,2, 0)) -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Check out... =LOOKUP(D2,A:A,B:B) -- Jacob (MVP - Excel) "Kaisa" wrote: A B C E F 1 SKU SUM SKU SUM 2 85 10 85 10 3 86 20 86 20 4 87 30 88 30 5 89 40 I'm trying to make a formula that can tell me the sum of each number. Like her, that 85 picks out that it's 10 and so on. But at 88, which isn't in the original list, it just takes the number above. I wish it could come up as an error, when it doesn't exist. The formula I have used here is: =VLOOKUP(D;A:B;2) |
Thread Tools | |
Display Modes | |
|
|