Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

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:
User generated image
Ex on Sheet 2:
User generated image
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:
User generated image
How can i do that?

Here is a sample
Sheet1-versus-Sheet-2.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Michael
Michael
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wilder1626

ASKER

Thanks a lot, This is what i was looking for.
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.
Thanks Shums Millwala for your option. That also is a good idea.