Vlookup multiple criteria

[brandon16]

Hi,
I am trying to create a vlookup based on multiple criteria but not quite sure how best to do this. I tried this formula but doesn't output how I want it to.

=VLOOKUP(G1055&B1055,'App Lookup'!$G$23:$J$36,3)

Below is my lookup table, tab called App Lookup.

My App_Data tab needs to be something like:

if app_no = 78 regardless of app_name then App Declined
if app_no = 85 regardless of app_name then App Declined
if app_no = 85 AND app_name = MOR then App Comp
else ignore app_no and where it matches the APP_NAME value then Output_Description

App_Name	App_No	Output_Description
	78	App Declined
	85	App Declined
MOR	85	App Comp
MOR		App Comp
PUP		App Processing
ETS		App Withdrawn

 

[KRice]

What version of Excel are you using? The version often dictates which approach(s) need to be taken for the solution to work for you. Please update your account details with that information.

Can you explain what information is found in the cells/ranges referenced in:

=VLOOKUP(G1055&B1055,'App Lookup'!$G$23:$J$36,3)

I'm assuming 'App Lookup'!$G$23:$J$36 refers to the table you've described, but that table consists of three columns and the range referenced refers to four columns (G:J), and you appear to want the third column returned. I am confused about the lookup range referenced, which covers 14 rows (23:36), while the lookup table you've shown has 6 rows. Could you clarify what information is where and whether you are showing only a small example of a larger set of lookup criteria. Also, what is G1055&B1055 intended to do?

It is likely that VLOOKUP is not suitable for this task, as it requires the lookup table to be sorted in ascending order.

 

[KRice]

I'm not clear about what you really want to do, but I think it might be easier to construct a formula that handles the exceptions with IF statements, and then after confirming that none of those apply, revert to a simple INDEX/MATCH formula. Then it would be better to restructure the lookup table (for the INDEX/MATCH) to eliminate those combinations that are already handled by the IF statements. So the lookup table looks like this:

MrExcel_20230706.xlsx
	G	H	I
22	App_Name	App_No	Output_Description
23	MOR		App Comp
24	PUP		App Processing
25	ETS		App Withdrawn
26
27
28
App Lookup

...and the main table like this:

MrExcel_20230706.xlsx
	B					G	H
1054	App #					App Name	Output
1055	78						App Declined
1056	85					MOR	App Comp
1057						PUP	App Processing
1058						MOR	App Comp
1059						ETS	App Withdrawn
1060	6					MOR	App Comp
1061	1					PUP	App Processing
1062	85					ETS	App Declined
App_Data

Cell Formulas
Range	Formula
H1055:H1062	H1055	=IF(AND(B1055=85,G1055="MOR"),"App Comp",IF(OR(B1055=78,B1055=85),"App Declined",INDEX('App Lookup'!$I$23:$I$30,MATCH(G1055,'App Lookup'!$G$23:$G$30,0))))