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]

Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Ecode​

[/TD]
[TD]

Date​

[/TD]
[TD]

Day​

[/TD]
[TD][/TD]
[TD]

Ecode​

[/TD]
[TD]

Date​

[/TD]
[TD]

Day​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

1/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0123​

[/TD]
[TD]

2/6/2018, Saturday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

EKCS-1141​

[/TD]
[TD]

1/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1246​

[/TD]
[TD]

13/5/2018, Sunday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

EKCS-0366​

[/TD]
[TD]

1/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0482​

[/TD]
[TD]

21/5/2018, Monday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

EKCS-1317​

[/TD]
[TD]

1/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0229​

[/TD]
[TD]

29/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[TD][/TD]
[TD]

EKCS-0362​

[/TD]
[TD]

15/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

EKCS-1141​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[TD][/TD]
[TD]

EKCS-0366​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

EKCS-0366​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[TD][/TD]
[TD]

EKCS-1317​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

EKCS-1317​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[TD][/TD]
[TD]

EKCS-1317​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1062​

[/TD]
[TD]

23/5/2018, Wednesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

EKCS-1141​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

EKCS-0366​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1188​

[/TD]
[TD]

25/5/2018, Friday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

EKCS-1317​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0683​

[/TD]
[TD]

31/5/2018, Thursday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1306​

[/TD]
[TD]

6/6/2018, Wednesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0366​

[/TD]
[TD]

1/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

8/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1141​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0331​

[/TD]
[TD]

9/5/2018, Wednesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1358​

[/TD]
[TD]

11/5/2018, Friday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1235​

[/TD]
[TD]

4/6/2018, Monday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1317​

[/TD]
[TD]

1/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

3/5/2018, Thursday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1141​

[/TD]
[TD]

1/5/2018, Tuesday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0366​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-1247​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD]

Holiday​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0949​

[/TD]
[TD]

7/5/2018, Monday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

EKCS-0911​

[/TD]
[TD]

27/5/2018, Sunday​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

28​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in G2 copied down
=IFERROR(""&INDEX($C$2:$C$14,MATCH(1,IF($A$2:$A$14=E9,IF($B$2:$B$14=F9,1)),0)),"")
Ctrl+Shift+Enter

M.

 

[GhostEcho]

If you're good with using VBA, I've got a code for that, it's pretty simple.

Public Function MyVlookup2(Mycrit As Variant, Searchit() As Variant, Mycrit2 As Variant, Searchit2() As Variant, Myresult() As Variant)

For i = 1 To UBound(Searchit)
If Searchit(i, 1) = Mycrit And Searchit2(i, 1) = Mycrit2 Then MyVlookup2 = Myresult(i, 1)
Next i


End Function

Think of it similar to a sumifs formula. MyVlookup2(1st column to search,1st criteria,2nd column to search, 2nd criteria, result column)

Very useful

Otherwise, you could combine the 2 columns of your criteria into one with an & and do a vlookup against that.

Hope that helps!

 

[Marcelo Branco]

oops...

Disregard my formula in post 2 - i pasted the wrong formula

Try in G2 copied down
=IFERROR(""&INDEX($C$2:$C$14,MATCH(1,IF($A$2:$A$14=E2,IF($B$2:$B$14=F2,1)),0)),"")
Ctrl+Shift+Enter

M.

 

[MASOODAHMAD]

Thanks Marcelo Branco, that's great. I need to learn more about Index and Match.
@GhostEcho, thanks for the code too. I'll give it a try someday. In the meantime, I'm trying to enhance my formula's skills.

Thanks again for your contributions.

 

[MASOODAHMAD]

oops...

Disregard my formula in post 2 - i pasted the wrong formula

Try in G2 copied down
=IFERROR(""&INDEX($C$2:$C$14,MATCH(1,IF($A$2:$A$14=E2,IF($B$2:$B$14=F2,1)),0)),"")
Ctrl+Shift+Enter

M.

No issues, I figured it out.

Once again, thanks for all your help.

 

[kvsrinivasamurthy]

Table1 is in Sheet1
Formula in Table2 in Sheet2 in C2 then drag down

=IF(COUNTIFS(Sheet1!$A$2:$A$14,A2,Sheet1!$B$2:$B$14,B2,Sheet1!$C$2:$C$14,"Holiday")>=1,"Holiday","")

 

[Marcelo Branco]

No issues, I figured it out.

Once again, thanks for all your help.

You are welcome. Thanks for the feedback.

M.

 

[MASOODAHMAD]

@kvsrinivasamurthy, thanks for the inputs. Your formula indeed worked well for this data, when the third condition is a "Holiday". But the formula by Marcelo is much robust as it extracts the data from the third column without looking at its content. As said earlier, "Holiday" is just an example, there could be wide variety of data in column C, and I would like to extract the same.

Thanks once again for all your inputs.

 

[JamesonMH]

oops...

Disregard my formula in post 2 - i pasted the wrong formula

Try in G2 copied down
=IFERROR(""&INDEX($C$2:$C$14,MATCH(1,IF($A$2:$A$14=E2,IF($B$2:$B$14=F2,1)),0)),"")
Ctrl+Shift+Enter

M.

Hi Marcelo,

Just wondering what the ""& does right before the INDEX function? I assume concatenating something, but not sure.

Thanks,
James