0

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!

pyromont
  • 3
  • 3
  • This is typically done through a combination of `INDEX()` and `MATCH()` or `VLOOKUP()` or one of the newer functions like `XLOOKUP()`. There should be plenty of examples around on SO. [Here](https://stackoverflow.com/a/58436658/9758194) is one. – JvdV Jan 04 '22 at 13:48
  • Replace IF MATCH with vlookup. It's simple to use, look up at google. Keep iferror. – Gedas Miksenas Jan 04 '22 at 13:59

1 Answers1

0

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.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • Thanks a lot for your help; I was not aware of VLOOKUP table usage, today I learned a new thing, thanks again :) – pyromont Jan 04 '22 at 14:19