Return cell if another Cell is present in column-CodePudding

In Excel sheet, I have a list of IDs (let's call it L1) and another list (L2) longer than L1; each element of L1 are present in L2; I have a list of texts linked to L1 too.

I would like to write a function that returns:

If a certain element of L2 is present in L1 display the text attached to L1;
If a certain element of L2 is not present in L1, display a standard text like "Not present";

In other words, I have this situation:

| L1 | Text | L2 |   Output  |
|----|------|----|---------- |
| c1 |test1 | c1 |   test1   |
| c2 |test2 | c2 |   test2   |
| c5 |test5 | c3 |Not present|
| c7 |test7 | c4 |Not present|
|    |      | c5 |   test5   |
|    |      | c6 |Not present|
|    |      | c7 |   test7   |

How can I reach this output? The point not clear to me is how to get the text, in fact, the function I wrote (mockup) doesn't return those values; such function is:

=IFERROR(IF(MATCH(C6;$A$2:$A$5;0)>0;B6);"Not Present")

Thanks for your help!

CodePudding user response：

You are very close, but this is the cigar :-)

=IFNA(VLOOKUP(C2,$A$2:$B$5,2,FALSE),"Not Present")

The VLookup() parameters mean the following:

C2        : look for C2 value
$A$2:$B$5 : look for that value inside that fixed matrix 
            (you will look in the first column)
2         : in case found, show the second column of $A$2:$B$5 where you found it. 
            In case not found, show `#N/A` error.
FALSE     : use an exact match, not an approximate one.

The =IFNA() function shows what to do in case of #N/A error.