Hi All,
Struggling with an excel problem. I have one list (list 1) with IDs and a corresponding value (letter).
Separately, i have a list of IDs (list 2) that I want to put the latest value for (see output columns for what i mean). I want to check first if a 2 -C exists, if not, check if a 2-B exists, whichever is the latest, display that value for the '2' ID.
Here's how i tackled it in excel:
Note - I simplified the below example for readability, the real problem has more complicated IDs and the letter column has more characters.
List 1:
<tbody>
</tbody>
List 2:
<tbody>
</tbody>
Struggling with an excel problem. I have one list (list 1) with IDs and a corresponding value (letter).
Separately, i have a list of IDs (list 2) that I want to put the latest value for (see output columns for what i mean). I want to check first if a 2 -C exists, if not, check if a 2-B exists, whichever is the latest, display that value for the '2' ID.
Here's how i tackled it in excel:
- created a column for concatenated values for list 1
- Wrote nested if statement in output column of list 2
- nested if statement:
- =IF(MATCH(CONCATENATE(D1,", ","C"),C;C,0),"C",
- IF(MATCH(CONCATENATE(D1,", ","B"),C;C,0),"B",
IF(MATCH(CONCATENATE(D1,", ","A"),C:C,0),"A",0)))
Error: the formula returns a #N/A value
Note - I simplified the below example for readability, the real problem has more complicated IDs and the letter column has more characters.
List 1:
<tbody> </tbody> | Letter |
<tbody> </tbody> | ||
1 | A | 1, A | ||
1 | B | 1, B | ||
1 | C | 1, C | ||
2 | A | 2, A | ||
2 | A | 2, A | ||
2 | B | 2, B | ||
2 | B | 2, B | ||
2 | B | 2, B |
<tbody>
</tbody>
List 2:
ID (column D) | Output |
1 | C |
1 | C |
2 | B |
2 | B |
<tbody>
</tbody>
Last edited: