LOOKUP function in Array or Table

[spinifex]

I can't find the solution anywhere online, and the basic "how to" wiki isn't helping either.|
While I appreciate all help offered, please don't respond with "use a database instead of a spreadsheet" as this isn't possible with my particular work situation.

I have a range of products we sell in my store, and the cost-prices for identical items is different, based on whether I purchased them in a special bulk-deal or not.

I'm trying to create a stock-count spreadsheet (sheet1) that will populate the "cost price" column with the correct details from sheet2, by applying some sort of lookup or match formula.
The problem is, I need the lookup function to check 4 different parameters within the array in sheet2, to determine the correct price to return.


The cost pricing is on sheet 2, and is dependent on a purchase code, eg: purchased 1 pack for a "base" price, or purchased 28 packs of product for a cheaper "bulk" price.


I'm stumped how to code a lookup function in sheet1, that will check all 4 parameters in that row, and return the appropriate result from the array in sheet2
ie: Sheet 1 contains the stock counts, and sheet 2 contains the variable pricing data.
Require: Sheet1.CellH2 to determine the correct price by matching-up/looking-up A2, B2, C2 & G2 in Sheet2, and returning the resulting value in Sheet2.ColumnE

How do I correctly set up the data in sheet 2? As a table, an array, or just a basic spreadsheet with cells filled with data?
And how do I get a lookup or match formula in sheet 1, to access them?

Apologies if it doesn't quite make sense - this is my first time on these forums and I appreciate any help offered.

[RusselB]

Welcome to the Forums.
Part of the problem, is the fact that you are trying to do multiple item searches, when the LOOKUP functions (LOOKUP, VLOOKUP, HLOOKUP, MATCH) only allow for a single search.
One way around this, is to create one (or more) helper columns, that contain all of the lookup possibilities.
There is a similar topic here

[spinifex]

Sincere thanks @RusselB for the prompt reply!

I'm trying to work out from that linked topic you've provided, but the original question seems to be missing his example table, so I can't really follow what his original query is, and how you solved it?

I think however, I've just had a brainwave - I have concatenated the 3 text fields in my query together (in another column), so the lookup function is searching for that single text variable (containing all 4 concatenated strings), within my sheet2 array (I'll add a column with concatenated strings there as well) and simply have my multiple-prices within the same row, in different columns, instead of spread over different rows.
as per:


I'm not sure if this is the best solution, but it works for my purposes for now.

But I'm still interested to read through that other thread if you can assist with the OP's original query, if you can access the missing example table? (or recreate it?)

[robleyd]

The only relevant info in that topic was the images posted by the OP and some text representing their data. See the first post (image) and fourth post (image and text).

[Villeroy]

First of all, a spreadsheet is not a database. This is trivial to do with a database. When it comes to list keeping and getting information out of lists, a database is difficult to set up but far easier to use than a spreadsheet.
LOOKUP does not do what most spreadsheet users expect it to do. It can not lookup distinct items. You have to use MATCH or VLOOKUP in database mode (last argument explicitly set 0).
Some kind of filter may do what you want on the spreadsheet. Filters hide non-matching rows. Filters can take up to 8 criteria values.