Match values in a different sheet

[ligia]

[SOLVED] Would anybody please help me create a formula that does the following?

Sheet #1 contains a list of product names in column A, their SKU’s in column B (alpha numeric codes, for example STAR10) and the number of products sold listed in column C, respectively.

Sheet #2 row #2 contains the some of the SKU’s listed in Sheet 1 in column B. I would like to create a formula that will be used in Sheet 2, row 3 that:
- matches the SKU in row 2 with the SKU in column B in worksheet 1, then
- looks up the value in column C (number of items sold)
- returns that value increased by 20%
- rounded up to 2 decimal places to the left of the decimal
- if a match is not found should return 0

Is this possible because after a few good hours of failed attempts I am ready to give up?
:-)

Thank you

[Pete_UK]

Try this:

=IF(ISNA(MATCH(B2,Sheet1!B:B,0)),0,ROUNDUP(VLOOKUP(B2,Sheet1!B:C,2,0)*1.2,2))

Hope this helps.

Pete

[ligia]

Wow, this is complicated but it worked, great stuff, you are good :-)! I have two comments though.
1- Currently the formula increases the number by 20 % which is fine, but it leaves out the requirement to round up to 2 decimal places to the left of the decimal. With other words:
the number 674 increased by 20% is 808.8 and the formula calculates it exactly. It needs to be rounded up to 810. Is this possible?

2- I dragged the formula horizontally, in sheet 2 row 2. It changed and referenced different cell numbers. Is there a trick to dragging the formula so it will look like below?

=IF(ISNA(MATCH(B2,Sheet1!B:B,0)),0,ROUNDUP(VLOOKUP(B2,Sheet1!B:C,2,0)*1.2,2))
=IF(ISNA(MATCH(C2,Sheet1!B:B,0)),0,ROUNDUP(VLOOKUP(C2,Sheet1!B:C,2,0)*1.2,2))
=IF(ISNA(MATCH(D2,Sheet1!B:B,0)),0,ROUNDUP(VLOOKUP(D2,Sheet1!B:C,2,0)*1.2,2))
=IF(ISNA(MATCH(E2,Sheet1!B:B,0)),0,ROUNDUP(VLOOKUP(E2,Sheet1!B:C,2,0)*1.2,2))

[FDibbins]

try absoluting...
=IF(ISNA(MATCH(B2,Sheet1!$B:$B,0)),0,ROUNDUP(VLOOKUP(B2,Sheet1!$B:$C,2,0)*1.2,0))

edit: and actually if you break it down, its not that scary...
=IF(ISNA(MATCH(B2,Sheet1!B:B,0)),0,ROUNDUP(VLOOKUP(B2,Sheet1!B:C,2,0)*1.2,2))

working from the inside out...
vlookup() is finding the SKU qty and applies +20% to it
roundup() takes that answer and, well, rounds it up (i changes it from ,2 to ,0)
match() is looking for B2 in sheet2
isna() is testing the match to make sure it was found
if() tests if the value was found, it not (isna(), it returns 0, else it returns the qty +20%

hope that helps?

[ligia]

The formula is perfect. Thank you both very much and thank you for the explanation.
Can you recommend a trick to copy the formula in such a way that the references made in the formula to cells in sheet 1 (eg Sheet1!$B:$B,0 or Sheet1!$B:$C,2,0 will remain unchanged?

If I drag the formula now, the references change to show $C:$C and $C:$D

[FDibbins]

i dont see how the $B:$B or $B:$C can change when you drag or copy them across, they have been absoluted, perhaps you meant
=IF(ISNA(MATCH(B2,Sheet1!$B:$B,0)),0,ROUNDUP(VLOOKUP(B2,Sheet1!$B:$C,2,0)*1.2,0))

in which case, change the B2 to $B2...this will ensure that the column number does not change as you copy across

there are 3 different absolutes
$A$1 absolutes the entire reference. this reference will not change, no matter where you move or copy it to
$A1 will absolute the column, the column reference will no change as you copy it across, but the row number will change as you copy down
A$1 will absolute the row number. the column will change as you copy across, but the row number will not change as you copy down

hope this helps?

[ligia]

Aha, I see how it works now. It worked like magic after several long hours of guess work on my behalf. Thank you again for the help and for the explanations. Take care.

[Pete_UK]

Glad you got it sorted in my absence.

Pete

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

[Pete_UK]

Hey, FDibbins, congratulations on 3000 posts !!

Pete

[FDibbins]

thanks Pete, you are on you're way there pretty soon too