Lookup within a lookup

[TimeForTim]

Hi,

I have a worksheet of products (bedside tables), each with a price and weight table. I would like to be able to lookup the SKU within the worksheet, then lookup a price (or weight) based on the dimensions. I was able to do it with a different set of products (beds) because the secondary attribute (mattress size) was always in the same position. Attached are two images of my worksheets (XL2BB just caused my worksheets to freeze when I attempted to use it). The first is the worksheet of the price and weight tables. The second is the functional lookup sheet. I need to be able to lookup the price and weight from the first sheet in the price and weight columns of the second. What I would like to be able to do is something like the following:

=INDEX("range of BT sheet", MATCH("SKU - here BT000a", "col A of BT sheet", 0), MATCH("dimension", "row of dimensions that correspond to the SKU", 0))

or written differently with the following named ranges

BT_RANGE - range of all cells in the bedside table price/weight table sheet
BT_COL - column A of bedside table sheet - contains the SKUs to be searched

=INDEX(BT_RANGE, MATCH("BT000A", BT_COL, 0), MATCH("12w 8d 6h", ???, 0)

The ??? in the second lookup is where I'm stuck. I know it will be the row from the first look, but I don't know how to dynamically reference that row.

(And there will be added modifiers into the formula to get to the correct row 1-8 (which corresponds to the wood type). I can handle that, but left out for clarity purposes.)

 

[shift-del]

(XL2BB just caused my worksheets to freeze when I attempted to use it).

Could you share a link to your sample workbook with the intended result?

 

[TimeForTim]

Could you share a link to your sample workbook with the intended result?

Google Sheets Sample Workbook

I pared down the info and put into a Google Sheet doc. The issue is with the 3rd MATCH formula in the formula in column D of the Output sheet. The formula is set up to work with the first SKU (BT000a) but fails for the second SKU (BT000P) since the dimensions list is different. I need to be able to match my desired dimensions with the dimensions list associated with each SKU (always starting 1 row below and 3 columns right of the SKU reference cell).

 

[shift-del]

I would suggest changing the structure of the lookup sheet.

Sample (25).xlsx
	A	B	C	D	E
1	Parent SKU	Description	Wood	Size	Price
2	BT000A	Floating bedside table	Cherry	12w 8d 6t	174
Tabelle1

Then it would be a XLOOKUP() or FILTER() with 3 criteria.

 

[TimeForTim]

Sorry for being dense. I could change the structure of the lookup sheet like you suggest (at least through a helper sheet, which is fine), but I still run into the issue of not being able to reference the dimensions array in the price table sheet based on the SKU I'm looking up.

FWIW I don't have tons of experience with either XLOOKUP or FILTER. I understand the concept of them.

 

[shift-del]

I think you misunderstood. I was talking about sheet "BT_ALL".
And there are millions of tutorials for XLOOKUP() and FILTER().

 

[TimeForTim]

Ah, can you elaborate on what changes you suggest to the "BT_ALL" sheet? I'm hesitant to change my structure because it would be a pretty big overhaul of how we are compiling our data.

 

[shift-del]

See #4

 

[TimeForTim]

See #4

I feel like I am entirely missing something. All I see with #4 is the attached image, which is just a reorganized snippet of my "Output" sheet. When I copy to clipboard and paste into an excel document, it's values only, so if there is a formula in E2, I am not able to see it.

 

[shift-del]

which is just a reorganized snippet of my "Output" sheet.

It is a reorganzied snippet of "BT_ALL".

I only put one row of data but in the end it would be more than 250 rows to cover all your products and prices.
And there are no formulas because sheet "BT_ALL" also has no formulas.