xlookup with multiple criteria

[Trev13]

Hello from Perth Australia

I have a problem I can’t solve.

I have a large data set and needing to return 5 different prices based on month of sale & a certain product model.

The two different words are in two different cells that change over time. I tried everything I can find on the web but no luck.

I’ve had a crack at it with an xlookup formula in C4 but still doesn’t really work, even for the first three columns!

It’s important that the return is invalid if one of the criteria words is not entered.

This table is simplified, the real one has 27 columns and 70 rows!

Can anyone help?
Trev

 

[Alex Blakenburg]

You are more likely to get a response if you provide an XL2BB sample of your data so that responders don't need to manually recreate your data.

"MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in"

See if this works for you:

20220310 Column and Row lookup Trev13.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	BASE MODEL	FRASER												MONTH	MODEL	PRICE	TTL	COMM	BONUS	SUPER BONUS	MONTH	MODEL	PRICE	TTL	COMM	BONUS	SUPER BONUS
2	MONTH SOLD	APRIL												MARCH	CHALLENGE	P3_2	T3_2	C3_2	B3_2	S3_2	APRIL	CHALLENGE	P4_2	T4_2	C4_2	B4_2	S4_2
3		Option1	Option2 - LET											MARCH	CANYON	P3_3	T3_3	C3_3	B3_3	S3_3	APRIL	FRASER	P4_3	T4_3	C4_3	B4_3	S4_3
4	PRICE	P4_3	P4_3											MARCH	FRASER	P3_4	T3_4	C3_4	B3_4	S3_4	APRIL	CANYON	P4_4	T4_4	C4_4	B4_4	S4_4
5	TTL	T4_3	T4_3											MARCH	SPACE	P3_5	T3_5	C3_5	B3_5	S3_5	APRIL	SPACE	P4_5	T4_5	C4_5	B4_5	S4_5
6	COMM	C4_3	C4_3											MARCH	OCEAN	P3_6	T3_6	C3_6	B3_6	S3_6	APRIL	OCEAN	P4_6	T4_6	C4_6	B4_6	S4_6
7	BONUS	B4_3	B4_3											MARCH	DRIVE	P3_7	T3_7	C3_7	B3_7	S3_7	APRIL	DRIVE	P4_7	T4_7	C4_7	B4_7	S4_7
8	SUPER BONUS	S4_3	S4_3
9
Final

Cell Formulas
Range	Formula
B4:B8	B4	=INDEX($N$2:$AA$7,MATCH($B$1,INDEX($N$2:$AA$7,0,MATCH($B$2,$N$2:$AA$2,0)+1),0),MATCH($B$2,$N$2:$AA$2,0)+MATCH($A4,$N$1:$AA$1,0)-1)
C4:C8	C4	=LET(rngDATA,$N$2:$AA$7, rngROW2,$N$2:$AA$2, rngHDG,$N$1:$AA$1, colMONTH,MATCH($B$2,rngROW2,0), INDEX(rngDATA, MATCH($B$1, INDEX(rngDATA, 0, colMONTH+1), 0), colMONTH+MATCH($A4,rngHDG,0)-1))

 

[Trev13]

Thank Alex,
I'm new to this forum, I tried to load the excel file but it wouldn't let me.
Not sure I even understand how to apply your solution to my workbook.
I assume these codes just replace the prices I had in there?
Regards
Trev

 

[Alex Blakenburg]

You can either create a new sheet and copy the whole thing into there.
Then copy either B4:B8 or C4:C8 into B4 on your real sheet.

OR

From the Cell formulas section pick either the B4 formula or the C4 formula,
highlight it and copy
paste it into B4 on your real sheet and copy it down to B8

 

[Alex Blakenburg]

That is assuming your picture actually starts from Row 1 since you have cut off the row numbers.

 

[Trev13]

I don't understand where all the prices have gone!

 

[Alex Blakenburg]

where all the prices have gone!

I don’t understand what you are referring to ?

Hopefully you didn’t copy my XL2BB over your data. If you want to copy what I posted by clicking on the copy button in the top left corner use a new empty sheet to paste it into.

I have logged off for the night.

 

[Trev13]

Thanks for your help Alex, what was referring to is the snip I posted show dollar figures, not T3-2 ETC..
However I have abandoned my original worksheet, I was over thinking it. But I do have a question based on a different chart.
This example attached is a simple vlookup which returns a result based on a product search word in green cell.
What I need to do is change the result in cells D2, E2 & F2 based on a second word (MO) shown in cell A4 (data on row 16)
example of what i'm trying to return is in row 6.
The real data set has hundreds of entries so can’t simply add a new set of data for, say ‘oracle mo’
Sorry I tried to create a file using the xl2bb add in but it just would not let me!
Can you help?
Trev

 

[Alex Blakenburg]

I don't understand the connection between Oracle and MO

 

[Trev13]

Ok no worries, thanks anyway