index match question - search column x for a value matching y

[RaviWildcat]

Hi Everyone - I thought I was pretty good with index match until I came across this puzzle.

I've got a pizza table with these columns

Brady	Friends	Partridge	Pizza Type
1	1		Cheese
		1	Pepperoni
2			Garlic
	2	2	Sausage

I'm allowing the user to select the name (Brady or Friends or Partridge) in one drop down and the number of guests (1 or 2) in another dropdown.

If the user selects the name Brady and 1, then serve a cheese pizza. If the user selects Brady and 2 it will be a garlic pizza.

If the user selects Partridge and 1 it will be a pepperoni. If it's partridge and 2 then it will be sausage

However!

I would like to use the column number in my index match and not the column name. so, if the user picks brady and 2, I want to select the first column of the pizza table and then look for the value 2, and then look across to see what pizza corresponds. This way I won't need to have multiple IF statements in my formula.

So, I'd have a separate table

name	column
Brady	1
Friends	2
Partridge	3

And, if the user selects "Friends" and "2" I'd know to refer to column 2 in my pizza table and then look for the value 2 in that column, and select a sausage pizza.

I can easily write something using multiple if statements "if user selects brady then index (pizza table, match (pizza table [brady], , if user selects friends then index pizza table match friends etc. ) but it just seems so clunky. And, I'm trying to avoid using a macro if a clever formula can solve the issue.

Any ideas?

 

[Cubist]

How about this?

Book1
	A	B	C	D	E	F	G
1	Brady	Friends	Partridge	Pizza Type		Friends	2
2	1	1		Cheese			Sausage
3			1	Pepperoni
4	2			Garlic
5		2	2	Sausage
Sheet5

Cell Formulas
Range	Formula
G2	G2	=TOCOL(IFS(F1&G1=A1:C1&A2:C5,D2:D5),2)

 

[RaviWildcat]

hey that's great and humbling too!

Great because it solves the problem
Humbling because I thought I was pretty good at excel but I had never heard of tocol or ifs!

Thank you!