CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Deklin
Deklin

Reputation: 49

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

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:

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:

Thanks in advance for any answers/help!

Upvotes: 2

Views: 174

Answers (3)

Argyll
Argyll

Reputation: 9875

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.

Upvotes: 0

marikamitsos
marikamitsos

Reputation: 10573

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

Upvotes: 2

Osm
Osm

Reputation: 2881

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 .

Upvotes: 0

Related Questions