V-lookup help

[Jacks18928]

Hello all,

I run a daily report which requires me to manually input LIVE & initial stock data (this doesn't change) columns against the SKU, colour and size (which is a pain)

Is there something in V-lookup which can pick up the sku, colour and size & pull in the LIVE and initial stock values?

Cheers in advance for any help

SKU COLOUR SIZE LIVE DATE INITIAL STOCK

WF1111 BLACK 6 01/08/2018 2
WF1111 BLACK 8 01/08/2018 5
WF1111 BLACK 10 01/08/2018 0

[pjwhitfield]

Whenever I need to do multi criteria lookups I create a helper column concatenating the items you need and then use this for the vlookup.

I've attached an example.

[Jacks18928]

This is great, how would you pull in the date data?
Cheers

[Glenn Kennedy]

...whereas I prefer NOT to concatenate/use helpers...

=IFERROR(INDEX(D$2:D$4,MATCH(1,INDEX(($A$2:$A$4=$J$2)*($B$2:$B$4=$K$2)*($C$2:$C$4=$L$2),0),0)),"")

copied across.

[Jacks18928]

Thank you both for your help
Its made my job a little easier

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[pjwhitfield]

...whereas I prefer NOT to concatenate/use helpers...

=IFERROR(INDEX(D$2:D$4,MATCH(1,INDEX(($A$2:$A$4=$J$2)*($B$2:$B$4=$K$2)*($C$2:$C$4=$L$2),0),0)),"")

copied across.

Horses for courses and a lot of it very much depends on the sheet itself, how it looks, works etc but in my case there's an additional element of future proofing. Often the workbooks I work on are then passed over to non "power" users and they have the ability to amend etc over time. The likes of the above can fill someone with dread and if something goes wrong can mean they lose the functionality until someone more experienced takes a look. I made the same sort of comment in a thread the other day with an array formula, the array solution was a perfect, compact and well executed answer whereas the alternative meant additional columns in the data.The positive of those columns though meant anyone with a basic Excel understanding could work out how the answer was derived and amend/ add to if required.

In our office we have to try and get a compromise between performance and ease of reading/ understanding in order to reduce potential issues down the line, this often however means losing out on using excellent formulas like the above.

[Glenn Kennedy]

Point taken. Where your formula will prform better (much better) than mine is if you use whole column references. Yours still steams along, whereas mine.. kinda slows up... a lot.

[Sam Capricci]

just to answer your post #3 for what pjwhitfield gave you, adjust the vlookup to this...
=VLOOKUP(K2&L2&M2,D2:E4,2,FALSE)

[congnt92]

Or try this

Please Login or Register  to view this content.

Where 3 is number of criteria.
It's not recommend to concatenate all criteria if we have a lot of criteria.