Vlookup or xlookup or index match multiple columns

[john62290]

Hey all, this is driving me crazy why I can't figure out a formula to look one column or the other based on a value in a cell of another table. In the seperate table I have a list of data that says with product A or Product B. If it says product B, I want it to look up the account name that matches for product B. Say my other table has Contract B and then account name ABC, I want it to return "Reorder." Then, if the next line of data is product A account name XYC, i want the formula to return "Contract." I have an if/then statement with two vlookups but I feel like there should be something more efficient. Thanks for the help!

Account Name	Product A	Product B
XYZ	Contract	Contract
ABC	New	Reorder
LMN	Portfolio	Portfolio
QRS	Dead	Contract

 

[Alex Blakenburg]

Do you mean like this:

Book3
	A	B	C	D	E	F	G
1	Account Name	Product A	Product B
2	XYZ	Contract	Contract			Product	Product B
3	ABC	New	Reorder			Acct Name	ABC
4	LMN	Portfolio	Portfolio
5	QRS	Dead	Contract			Result	Reorder
6
Sheet1

Cell Formulas
Range	Formula
G5	G5	=XLOOKUP($G$3,$A$2:$A$5,XLOOKUP(G2,$B$1:$C$1,$B$2:$C$5),"")

 

[Cubist]

Something like this?

Book2
	A	B	C	D	E	F	G
1	Account Name	Product A	Product B		Account Name	Product
2	XYZ	Contract	Contract		XYZ	Product A	Contract
3	ABC	New	Reorder		ABC	Product B	Reorder
4	LMN	Portfolio	Portfolio
5	QRS	Dead	Contract
Sheet4

Cell Formulas
Range	Formula
G2:G3	G2	=TOCOL(IFS(($A$2:$A$5=E2)*($B$1:$C$1=F2),$B$2:$C$5),2)

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Account Name	Product A	Product B		Account Name	Product
2	XYZ	Contract	Contract		XYZ	Product A	Contract
3	ABC	New	Reorder		ABC	Product B	Reorder
4	LMN	Portfolio	Portfolio
5	QRS	Dead	Contract
6
Data

Cell Formulas
Range	Formula
G2:G3	G2	=INDEX($B$2:$C$100,XMATCH(E2,$A$2:$A$100),XMATCH(F2,$B$1:$C$1))