Results 1 to 2 of 2

Thread: Udf vlookup
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Udf vlookup

    I have a table of items that are assigned a fixed cost.
    Example:
    Item) Name) Price
    1) Red Ruby Shirt) $35.22
    2) Blue Tuxedo) $47.90

    On a separate sheet I have a UserForm with a list box that grabs the table from the other sheet. I would like to sometimes override the fixed price aka “mark it up” for certain occasions (ex: Red Ruby Shirt needs extra stitching). But if I were to produce 1000 units of this item, in my ERP system it is set a fixed cost, I need a UDF that helps the ERP team know that I “marked the price up”

    Example:
    Item) Name) Price) UDF
    1) Red Ruby Shirt) $37.32) “$2.10 higher”
    2) Blue Tuxedo) $47.90) “No markup”
    Last edited by VBE313; Sep 12th, 2019 at 05:18 PM.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,005
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Udf vlookup

    Why do you need a UDF? Could we use standard worksheet formulas like this?
    I've done this on a single sheet but it could easily be adapted to two.

    Check Mark Up

     ABCDEFGH
    1Item Name Price Item Name PriceMark Up?
    21Red Ruby Shirt$35.22  1Red Ruby Shirt$37.32 $2.10 higher
    32Blue Tuxedo$47.90  2Blue Tuxedo$47.90 No markup
    4    1Red Ruby Shirt$34.00 $1.22 lower

    Spreadsheet Formulas
    CellFormula
    H2=IF(VLOOKUP(E2,A$2:C$3,3,0)=G2,"No markup",TEXT(ABS(VLOOKUP(E2,A$2:C$3,3,0)-G2),"$0.00")&IF(VLOOKUP(E2,A$2:C$3,3,0)<G2," higher"," lower"))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •