Lookup if two conditions are met!

[MASOODAHMAD]

Hello,

I'm trying to get this to work. I tried the vlookup, combination of Match & Index, but none helped me to achieve the desired output. I'm missing something.

Here is my lookup table1 with "Holiday" details in the third column. Instead of "Holiday", the column might contains other text/value.

Table1:
[TABLE="width: 251"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Ecode[/TD]
[TD="class: xl65, width: 161"]Date[/TD]
[TD="class: xl65, width: 87"]Day[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1247
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday
[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1141[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday
[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-0366[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday
[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1317[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { border: 0.5pt solid windowtext; }.xl65 { font-weight: 700; }.xl66 { text-align: left; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl67 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }</style>

Below is the table2 where I want to extract the values from the table1 above.

If values in column A and B (table2) matches with the values in column A and B (table1), then the corresponding column C (table2) should get the value from column C of table 1. Also note that Ecode is repeated in column A.

For example, in the table below, the Red highlighted needs to have the "Holiday" data in column C.

Table2:
[TABLE="width: 605"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Ecode[/TD]
[TD="class: xl65, width: 161"]Date[/TD]
[TD="class: xl65, width: 87"]Day[/TD]
[TD="width: 25"][/TD]
[TD="width: 87"][/TD]
[TD="width: 25"][/TD]
[TD="class: xl65, width: 87"]Ecode[/TD]
[TD="class: xl65, width: 161"]Date[/TD]
[TD="class: xl65, width: 87"]Day[/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0123[/TD]
[TD="class: xl64, align: right"]2/6/2018, Saturday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0123[/TD]
[TD="class: xl64, align: right"]2/6/2018, Saturday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1246[/TD]
[TD="class: xl64, align: right"]13/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1246[/TD]
[TD="class: xl64, align: right"]13/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0482[/TD]
[TD="class: xl64, align: right"]21/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0482[/TD]
[TD="class: xl64, align: right"]21/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0229[/TD]
[TD="class: xl64, align: right"]29/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0229[/TD]
[TD="class: xl64, align: right"]29/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0362[/TD]
[TD="class: xl64, align: right"]15/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0362[/TD]
[TD="class: xl64, align: right"]15/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1317
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-1317[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1062[/TD]
[TD="class: xl64, align: right"]23/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1062[/TD]
[TD="class: xl64, align: right"]23/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1188[/TD]
[TD="class: xl64, align: right"]25/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1188[/TD]
[TD="class: xl64, align: right"]25/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0683[/TD]
[TD="class: xl64, align: right"]31/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD]OUTPUT >>[/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0683[/TD]
[TD="class: xl64, align: right"]31/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1306[/TD]
[TD="class: xl64, align: right"]6/6/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1306[/TD]
[TD="class: xl64, align: right"]6/6/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1141
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-1141[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0331[/TD]
[TD="class: xl64, align: right"]9/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0331[/TD]
[TD="class: xl64, align: right"]9/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1358[/TD]
[TD="class: xl64, align: right"]11/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1358[/TD]
[TD="class: xl64, align: right"]11/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1235[/TD]
[TD="class: xl64, align: right"]4/6/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1235[/TD]
[TD="class: xl64, align: right"]4/6/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]3/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]3/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-0366
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-0366[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1247[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-1247[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0949[/TD]
[TD="class: xl64, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0949[/TD]
[TD="class: xl64, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0911[/TD]
[TD="class: xl64, align: right"]27/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0911[/TD]
[TD="class: xl64, align: right"]27/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { border: 0.5pt solid windowtext; }.xl65 { font-weight: 700; }.xl66 { text-align: left; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl67 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl68 { border: 0.5pt solid windowtext; }</style>

Thanks in advance.

 

[Marcelo Branco]

Hi Marcelo,

Thanks again for your inputs. Your formula did worked if the tables are identical, but don't if they are not. The Employee code in Column A could be in Random order, therefore, needs something which will look and verify the Employee Code and date from the three table and then come up with the results.

Just like we did in the previous scenario by matching to conditions using IF.

Ok, then you should use INDEX(table,match,match)
Something like
B2 copied across and down
=IF(INDEX(Sheet2!$A$1:$K$10,MATCH($A2,Sheet2!$A$1:$A$10,0),MATCH(B$1,Sheet2!$A$1:$K$1,0))="L","L",INDEX(Sheet1!$A$1:$K$10,MATCH($A2,Sheet1!$A$1:$A$10,0),MATCH(B$1,Sheet1!$A$1:$K$1,0)))

M.

 

[MASOODAHMAD]

You're a genius Marcelo,

I think the formula worked, I'll test it further by tomorrow.

Thanks for all your support.