Wilder1626
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:
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Then in Sheet1 in Column G, where you want to get desired result, enter below formula
Drag the formula in Sheet1 from Column G2 till your last row.
=A2&"_"&B2
Then in Sheet1 in Column G, where you want to get desired result, enter below formula
=IF(OR($F2>IFERROR(VLOOKUP($C2,Sheet 2!$C:$H,4, 0),""),$E2 >IFERROR(V LOOKUP($C2 ,Sheet2!$C :$H,3,0)," "),$D2>IFE RROR(VLOOK UP($C2,She et2!$C:$H, 2,0),"")), IFERROR(VL OOKUP($C2, Sheet2!$C: $H,6,0),"" ),IFERROR( VLOOKUP($C 2,Sheet2!$ C:$H,5,0), ""))
Drag the formula in Sheet1 from Column G2 till your last row.
ASKER
Thanks Shums Millwala for your option. That also is a good idea.
ASKER