Improve company productivity with a Business Account.Sign Up

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

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:
Sheet1
Ex on Sheet 2:
Sheet2
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:
Sheet 1 result
How can i do that?

Here is a sample
Sheet1-versus-Sheet-2.xlsx
0
Wilder1626
Asked:
Wilder1626
  • 2
1 Solution
 
MichaelBusiness AnalystCommented:
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
 
Wilder1626Author Commented:
Thanks a lot, This is what i was looking for.
0
 
ShumsDistinguished 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
 
Wilder1626Author 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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now