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

Pete Incomplete

New member
Joined
May 10, 2014
Messages
2
Reaction score
0
Points
0
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
ModelPrice
5912000
3998900
3499800
52934800

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 M90940FXT6722000


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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Use

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

Pete Incomplete

New member
Joined
May 10, 2014
Messages
2
Reaction score
0
Points
0
hello, thanks for the reply Bob,
bob said:
=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? (" -- ")
 
Top