'Select cell C where value in column A equals a and value in column B equals b
I have 2 tables, the first one contains an id, and the latest date the person was called and the comment associated with the phone call:
| id | latest call | call comments |
|---|---|---|
| 1 | 1/1/2021 | comments 1 |
| 2 | 1/12/2020 | comments 2 |
| 3 | 1/1/2021 | comments 3 |
| 4 | not contacted |
My second table contains all the phone calls:
| id | call date | call comments |
|---|---|---|
| 1 | 1/1/2021 | comments 1 |
| 3 | 1/1/2019 | comments |
| 3 | 1/1/2021 | comments 3 |
| 1 | 1/1/2021 | comments |
| 2 | 1/11/2019 | comments |
| 2 | 1/12/2020 | comments 2 |
| 1 | 10/1/2020 | comments |
The formula I used for latest call is:
=IF(MAX(IF(table_2_ids_range=table_1_id_cell, table_2_date_range)) > 0, MAX(IF(table_2_ids_range=table_1_id_cell, table_2_date_range)), "Not contacted")
Which returns the latest call date
However, I'm not sure how to get the comment associated with it, I'd like to do somethings like
SELECT cell in column "call comments" WHERE cell in column "id" equals VALUE1 and cell in column "call date" equals VALUE2
I tried
=IF(AND(table_1_id_cell=table_2_id_range,table_1_date_cell=table_2_date_range),table_2_comments_range,"")
but it just returned an empty string
Thanks!
Solution 1:[1]
combine each id and date on second table as second table's record id.
in first table , simply use vlookup vlookup(id&date, a:c, 3,0)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Meelad Ghazipour |