How do I utilize VLOOKUP in multiple instances? (Google Sheets)

user_19512715 0
Viewed 60

Slightly complex problem, but hopefully manageable.

Refer to this demo document before reading as I'll be referring to it throughout this post.

What I'm trying to accomplish: As seen in the demo document, I'm trying to insert "Sticker" images automatically if the following statements are correct:

  • Within the "Master Sheet" sheet; If the "SKU" number (column 'E') is found on the 'PRICE CHANGE' sheet, Then look under Column 'K' on the 'PRICE CHANGE' sheet.
  • Within the 'PRICE CHANGE' sheet; If the start of the of the text (on column 'K') start's with a number, find the number that matches on the 'Sticker Images' sheet under column 'B' and insert the image of the corresponding "Sticker" into the "Master Sheet" sheet.

In spoken terms: I want to fetch data from 'Master Sheet' sheet, refer that data to the 'Price Change' sheet, fetch more data from the 'Price Change' sheet, then refer that data to the 'Sticker Images' sheet, then finally bring the correct image from the 'Sticker Images' sheet and place the image in its correct spot on the 'Master Sheet' sheet.

Here are some visuals if my explanation still wasn't good enough

enter image description here enter image description here enter image description here enter image description here

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!

google-sheets-formula google-sheets
3 Answers

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)))
user_15277800

Try this

=IFERROR( VLOOKUP( IFERROR( LEFT( VLOOKUP( $E12, 'PRICE CHANGE'!$E$18:AC, 7, 0), 1)), 'Sticker Images'!B:C, 2, 1))

enter image description here

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 .

user_195296940

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.

user_31811040
Related