To compare two listings for matches, the XMATCH can be used by you function along with IF and ISNA:

To compare two listings for matches, the XMATCH can be used by you function along with IF and ISNA: For instance, to compare List 2 in B2:B10 against List 1 in A2:A10, the formula takes the next kind: =IF(ISNA(XMATCH(B2:B10, A2:A9)), «», «Match in List 1») In this instance, we identify just matches, and so the value_if_true argument regarding the IF function is a string that is empty»»). Enter the formula that is above the topmost cellular (C2 inside our situation), press Enter , and it surely will «spill» in to the other cells automatically (its known as a spill range): Exactly just exactly How this formula works At the heart for the formula, the XMATCH function pursuit of a value from List 2 within List 1. If your value is available, its position that is relative is, otherwise a #N/A mistake. Inside our situation, the consequence of XMATCH could be the following array: This array is «fed» in to the ISNA function to be examined for #N/A errors. For every single #N/A mistake, ISNA comes back REAL; for almost any other value — FALSE. Given that outcome, it produces the following variety of rational values, where REAL’s express non-matches, and FALSE’s express matches: The above array goes towards the rational test for the IF function. According to the manner in which you configured the past two arguments, the formula will output the matching text. Within our instance, it is a string that is empty»») for non-matches (value_if_true) and «Match in List 1» for matches (value_if_false). INDEX XMATCH in Succeed XMATCH may be used in conjunction with the INDEX function...
Яндекс.Метрика