x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 115

# Microsoft Excel formula with multiple criterias based on 2 sheets

Hi,

I would like to create a formula that will look at multiple criteria.

I will try to explain clearly.

I have a sheet 1 that i would like to validate the Rank based on a match in the name and city column and also if the value from table 1or table 2 or table 3 from Sheet1 are greater then the value in table 1or table 2 or table 3 from Sheet2. If one of the table values are greater than the sheet 2, it will result to Rank 2 value, else it will take the Rank 1 value from Sheet 2.

Ex on sheet 1:

Ex on Sheet 2:

Since on Sheet 1, Name Robert and City Guelf exist in Sheet 2,
then it will verify if Table 1 from sheet 1 => than Table 1 from sheet 2
or
Table 2 from sheet 1 = > than Table 2 from sheet 2
or
Table 3 from sheet 1 => than Table 3 from sheet 2.

If one of the tables are greater that the ones from Sheet2, then it will pull the value from Rank 2. It the result is <, than it will give the value from Rank 1.

If there is a not match on the name and city, it will say "No_Match" for the rank.

An example of the result based on the 2 first images above would be:

How can i do that?

Here is a sample
Sheet1-versus-Sheet-2.xlsx
0
Wilder1626
• 2
1 Solution

Try the following array formula:

=IFERROR(IF(OR(C2>=VLOOKUP(A2&B2,CHOOSE({1,2},Sheet2!\$A\$2:\$A\$100&Sheet2!\$B\$2:\$B\$100,Sheet2!\$C\$2:\$C\$100),2,0),D2>=VLOOKUP(A2&B2,CHOOSE({1,2},Sheet2!\$A\$2:\$A\$100&Sheet2!\$B\$2:\$B\$100,Sheet2!\$D\$2:\$D\$100),2,0),E2>=VLOOKUP(A2&B2,CHOOSE({1,2},Sheet2!\$A\$2:\$A\$100&Sheet2!\$B\$2:\$B\$100,Sheet2!\$E\$2:\$E\$100),2,0)),VLOOKUP(A2&B2,CHOOSE({1,2},Sheet2!\$A\$2:\$A\$100&Sheet2!\$B\$2:\$B\$100,Sheet2!\$G\$2:\$G\$100),2,0),VLOOKUP(A2&B2,CHOOSE({1,2},Sheet2!\$A\$2:\$A\$100&Sheet2!\$B\$2:\$B\$100,Sheet2!\$F\$2:\$F\$100),2,0)),"No_Match")

and enter the formula with ctrl+shift+enter

Edit: Here's an explanation on the topic and multiple ways to tackle the issue.
Sheet1-versus-Sheet-2.xlsx
0

Author Commented:
Thanks a lot, This is what i was looking for.
0

Distinguished Expert - 2017Commented:
Add one more column after column B in both the sheet, name the column header as "Combination" and enter below formula in both the sheets

=A2&"_"&B2

Then in Sheet1 in Column G, where you want to get desired result, enter below formula
=IF(OR(\$F2>IFERROR(VLOOKUP(\$C2,Sheet2!\$C:\$H,4,0),""),\$E2>IFERROR(VLOOKUP(\$C2,Sheet2!\$C:\$H,3,0),""),\$D2>IFERROR(VLOOKUP(\$C2,Sheet2!\$C:\$H,2,0),"")),IFERROR(VLOOKUP(\$C2,Sheet2!\$C:\$H,6,0),""),IFERROR(VLOOKUP(\$C2,Sheet2!\$C:\$H,5,0),""))

Drag the formula in Sheet1 from Column G2 till your last row.
0

Author Commented:
Thanks Shums Millwala for your option. That also is a good idea.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.