Results 1 to 3 of 3

Thread: Quagmire of Formulas (can't use macros or programming)

  1. #1

    Post Quagmire of Formulas (can't use macros or programming)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, thanks for seeing what my issue is. It's slightly convoluted so if I'm unclear PLEASE ask me to clarify. I'll do my best.

    Just to start off - programming languages are impossible for me and time consuming (this is done in my spare time for work, not a pay-time project), so I'd really prefer that any options explored include cell formulas only please...

    I need to paste between 1 and 10 items into cells (9 rows of 1 column). The syntax of the data is [model][space][serial_number] and looks like this: 591 M94440FXT672

    I currently have a table of 2 columns, wherein the model number is referenced in Column A, and a number value related to price is listed in Column B, like so:
    MASTER LIST
    Model Price
    591 2000
    399 8900
    349 9800
    529 34800

    I'd like to make it so I can paste several items into cells and it would appear as such (paste into column 1 only):
    PASTING TABLE
    591 M90940FXT672 $
    349 073CLPNVZNW8 $
    399 93YBNE8BK493582 $

    Ideally, I'd prefer if the spacing between model and serial be used to separate the model and serial by columns as well (if there's a handy formula for this)

    When pasting into the columns , the fields next to it would need to look up the model number (591, 349, etc), and reference the price column from the first table, without getting stuck referencing the serial number.

    So after pasting the details in, each row would have something like (formula in second column):
    PASTED DETAIL: Pop. lookup Result:
    591 M90940FXT672 2000


    Any help or advice using lookup, hlookup, vlookup, wildcards, etc. would be much appreciated! My sticking point is I cannot find a way to incorporate a wildcard so the lookup formulas ignore the serial number when referencing the price column from the master table. I'm willing to use several sheets, and many columns. Whatever is required. Thanks in advance for any response.

  2. #2
    Use

    =VLOOKUP(--(LEFT(A2,FIND(" ",A2)-1)),MasterList,2,FALSE)

  3. #3
    hello, thanks for the reply Bob,
    Quote Originally Posted by bob
    =VLOOKUP(--(LEFT(A2,FIND(" ",A2)-1)),MasterList,2,FALSE)
    in this case are there values I would put in place where you have typed the 2 hyphens? (" -- ")

Tags for this Thread

Posting Permissions

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