Slightly complex problem, but hopefully manageable.
Refer to this 
The problem I'm having: I can't seem to find a way to make the formula understand I'm looking for just the single value only at the START of the text on column 'K' within the 'PRICE CHANGE' sheet.
Here is the formula I'm using at the moment:
=IFERROR( VLOOKUP( IFERROR( LEFT( VLOOKUP( $E12, 'PRICE CHANGE'!$E$18:AC25, 12, 0), 1)), 'Sticker Images'!B:C, 2, 1))
Things to keep in mind:
- I cannot edit the 'PRICE CHANGE' sheet in any way.
- The "Men's Ultraboost 22 Running Shoe" should have a yellow sticker and the "Women's GEL-Kayano® 28 AWL Running Shoe" should have a red one (Just for validation/Check your work).
Thanks in advance for any answers/help!
CodePudding user response：
You can use a single, simplified formula for all.
Within your Master SheetClear everything in the range F12:F and place this formula in cell F12
=INDEX(IFERROR(VLOOKUP(LEFT(
VLOOKUP(E12:E,'PRICE CHANGE'!E19:K,7,0)),'Sticker Images'!B:C,2)))
CodePudding user response：
Try this
=IFERROR( VLOOKUP( IFERROR( LEFT( VLOOKUP( $E12, 'PRICE CHANGE'!$E$18:AC, 7, 0), 1)), 'Sticker Images'!B:C, 2, 1))
Keep the vlookup range open ended 'PRICE CHANGE'!$E$18:AC
The index in your formula is set to 12 is suppose to be set 7 to get the column K .
CodePudding user response：
The existing formula already works, as long as typo (or typo-like) errors are fixed:
=VLOOKUP(LEFT(VLOOKUP($E12, 'PRICE CHANGE'!E:K, 7, 0), 1)), 'Sticker Images'!B:C, 2, 1))
Note the 7 instead of 12. The key thing is to fix the column index for range E:K. (And have the correct row index for that range, and correct fixed vs iterative indices choices. I also changed AC to K since you only are referring to column K. May as well avoid a potential source of error by referring a larger range than you intend to have sheet content.)
I did test using Google Sheet. No guarantee how things turn out in Excel.
I don't usually post an answer for fixing small errors. The OP has everything correctly set up already. But posted nonetheless as requested.
CodePudding user response：
Three things:
1. VLOOKUP column reference
As Argyll said, your PRICE CHANGE column reference is incorrect. In the inner VLOOKUP() call it is currently indicated as the 12th column. You probably used 12 because you were counting starting from column 'A'. But, the count starts from the first column of the data range ($E$18:AC25); thus, column 1 is actually $E. So, '12' needs to be '7'.
2. Drawing tool objects vs. cell values (and cell formulas)
It isn't clear if you used some kind of shape-drawing tool to add those sticker image circles to the Sticker Images sheet (I might not have had the spreadsheets rendered properly in my browser). I'm not sure you can just copy those circles as if they were cell values or cell formulas. Those images might be floating on top of the spreadsheet and not actually linked to those cells. To copy those circles into the Master Sheet, you might need to reference them in some way that the reference (cell value) can be copied. For example, rather than using shape-drawings, you could use a code point from a font that displays a circle (e.g. the 'n' character from the Webdings font produces a circle). You can apply a large font size and different font colors to match what you currently have in the Sticker Images sheet.
3. VLOOKUP and INDEX
To achieve your goal of copying the circles (as cell values) from the Sticker Images sheet to the Master Sheet, the outer VLOOKUP() could instead be an INDEX() call. The result of the changes would look something like this:
=IFERROR( INDEX( 'Sticker Images'!$B$2:$C$4, IFERROR( LEFT( VLOOKUP( $E12, 'PRICE CHANGE'!$E$18:AC25, 12, 0), 1)), 2 ))
INDEX will retrieve the values in the cells from Sticker Images and copy them to Master Sheet.
Your original formula has a number (as a text string) as the first argument to the outer VLOOKUP() call. If Google Sheets does automatic data type conversion when comparing text to numbers then your outer VLOOKUP() call should be fine (taking item #2 above into account).