Search for cross reference value within a cell

Can you upload example workbook with your input and desired ouput?

make sure to avoid private informations.


Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Example.xlsx

Thanks Zbor,

In a nutshell, based on Main! column D and Colors! column B - Solve for Main! E & F.

I know it's possible, but I'm getting older trying to get it right.

hope this is what you're trying to do.

For color column, try this formula

=INDEX(Colors!$A$2:$A$11,MATCH(TRIM(LEFT(MID($D2,FIND("/",$D2)+1,3),MIN(FIND({0,1,2,3,4,5,6,7,8,9,"/"},MID($D2,FIND("/",$D2)+1,3)&"0123456789/"))-1)),Colors!$B$2:$B$11,0),0)

For quality column, try this formula

=VLOOKUP(E2,Colors!$A$2:$C$11,3,FALSE)

Thanks igoodable.

It's great.

Is there a way of changing it slightly so that instead of looking within the "/"s for a value, the value itself is found? I only ask, because these references are not always generated at my work and sometimes have a slightly different format.

the key part of the formula is to identify the "pu" from 10/pu/67, so if "pu" is always the 4th character, you can replace the entire find formula with a number 4. =INDEX(Colors!$A:$A,MATCH(MID($D2,4,2),Colors!$B:$B,0))

Thanks again.

It's close to what I'm looking for, but the input references are a bit too inconsistent for this formula. I may have to play around with it to get it to work properly. You've given me a few ideas to try.

Cheers.

Oh Haseeb!

You're a total machine! That's genius. I had worked it out using a massive formula but this is simplicity and elegance.

Bravo.