누락된 값 찾기

Excel 에서 목록 A 의 값이 목록 B 에 존재하는지 두 목록을 비교해야 하는 경우가 있습니다。 예를 들어 제품 목록이 있고， 사용자의 목록에 있는 제품이 공급업체가 제공한 제품 목록에 존재하는지 확인하고자 할 수 있습니다。 이 작업을 수행하기 위해 아래에 세 가지 방법을 제시했으니 원하는 방법을 선택하시기 바랍니다。

MATCH, ISNA 및 IF 를 사용하여 누락된 값 찾기
VLOOKUP, ISNA 및 IF 를 사용하여 누락된 값 찾기
COUNTIF 및 IF 를 사용하여 누락된 값 찾기

MATCH, ISNA 및 IF 를 사용하여 누락된 값 찾기

위의 스크린샷과 같이사용자의 목록에 있는 모든 제품이 공급업체 목록에 존재하는지 확인하려면먼저 MATCH 함수를 사용하여 사용자 목록(목록 A)의 제품을 공급업체 목록(목록 B)에서 위치를 검색할 수 있습니다. 제품을 찾지 못하면 MATCH 는 #N/A 오류를 반환합니다. 그런 다음 이 결과를 ISNA 함수에 전달하여 #N/A 오류를 TRUE 로 변환할 수 있으며， 이는 해당 제품이 누락되었음을 의미합니다。 이후 IF 함수가 원하는 결과를 반환합니다。

일반 구문

=IF(ISNA(MATCH()))「lookup_value」,lookup_range,0)),「Missing」,「Found」)

√ 참고： 「Missing」， 「Found」는 필요에 따라 원하는 값으로 변경할 수 있습니다。

확인하려면사용자의 목록에 있는 모든 제품이 공급업체 목록에 존재하는지아래 수식을 H6 셀에 복사하거나 입력한 후Enter키를 눌러 결과를 얻으세요。

=IF(ISNA(MATCH()))30002,$B$6:$B$10,0)),「Missing」,「Found」)

또는 셀 참조를 사용하여 수식을 동적으로 만들 수 있습니다。

=IF(ISNA(MATCH()))G6,$B$6:$B$10,0)),「Missing」,「Found」)

√ 참고： 위의 달러 기호($)는 절대 참조를 나타내며， 이는 수식을 다른 셀로 이동하거나 복사할 때lookup_range가 변경되지 않음을 의미합니다。 그러나lookup_value는 동적으로 유지되도록 하기 위해 달러 기호가 추가되지 않았습니다。 수식을 입력한 후 채우기 핸들을 아래로 드래그하여 아래 셀에도 수식을 적용하세요。

수식 설명

다음 수식을 예시로 사용하겠습니다。

=IF()ISNA()MATCH(G8,$B$6:$B$10,0)),「Missing」,「Found」)

• MATCH(G8,$B$6:$B$10,0):match_type 0은 MATCH 함수가 G8 셀의 값인3004을 배열$B$6:$B$10에서 처음으로 일치하는 항목의 위치를 나타내는 숫자 값을 반환하도록 강제합니다。 그러나 이 경우 MATCH 함수는 lookup 배열에서 해당 값을 찾지 못했으므로#N/A오류를 반환합니다。
• ISNA()MATCH(G8,$B$6:$B$10,0))=ISNA()#N/A):ISNA 함수는 값이 “#N/A” 오류인지 여부를 확인합니다. 그렇다면 함수는 TRUE 를 반환하고, “#N/A” 오류가 아닌 다른 값이라면 FALSE 를 반환합니다。 따라서 이 ISNA 수식은TRUE를 반환합니다。
• IF()ISNA()MATCH(G8,$B$6:$B$10,0)),「Missing」,「Found」) = IF(TRUE,「Missing」,「Found」):ISNA 및 MATCH 함수의 비교 결과가 TRUE 이면 IF 함수는 Missing 을 반환하고, 그렇지 않으면 Found 를 반환합니다。 따라서 이 수식은Missing를 반환합니다。

VLOOKUP, ISNA 및 IF 를 사용하여 누락된 값 찾기

귀하의 목록에 있는 모든 제품이 공급업체 목록에 존재하는지 확인하려면 위의 MATCH 함수를 VLOOKUP 으로 대체할 수 있습니다. VLOOKUP 은 값이 다른 목록에 존재하지 않을 경우(즉, 누락된 경우) #N/A 오류를 반환한다는 점에서 MATCH 와 동일하게 작동하기 때문입니다。

일반 구문

=IF(ISNA(VLOOKUP()))「lookup_value」,lookup_range,1,FALSE)),「Missing」,「Found」)

√ 참고： 「Missing」， 「Found」는 필요에 따라 원하는 값으로 변경할 수 있습니다。

귀하의 목록에 있는 모든 제품이 공급업체 목록에 존재하는지 확인하려면 아래 수식을 H6 셀에 복사하거나 입력한 후Enter키를 눌러 결과를 확인하세요。

=IF(ISNA(VLOOKUP()))30002,$B$6:$B$10,1,FALSE)),「Missing」,「Found」)

또는 셀 참조를 사용하여 수식을 동적으로 만들 수 있습니다。

=IF(ISNA(VLOOKUP()))G6,$B$6:$B$10,1,FALSE)),「Missing」,「Found」)

√ 참고： 위의 달러 기호($)는 절대 참조를 나타내며， 이는lookup_range가 수식을 다른 셀로 이동하거나 복사할 때 변경되지 않음을 의미합니다。 그러나lookup_value는 동적으로 유지되도록 하기 위해 달러 기호가 추가되지 않았습니다。 수식을 입력한 후 채우기 핸들을 아래로 드래그하여 아래 셀에도 수식을 적용하세요。

수식 설명

다음 수식을 예시로 사용하겠습니다。

=IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),「Missing」,「Found」)

• VLOOKUP(G8,$B$6:$B$10,1,FALSE):range_lookup FALSE는 VLOOKUP 함수가 G8 셀의 값인3004과 정확히 일치하는 값을 조회하여 반환하도록 강제합니다。 만약 lookup_value 3004가 배열1의 첫 번째 열인$B$6:$B$10에 존재하면 VLOOKUP 은 해당 값을 반환하고, 그렇지 않으면 #N/A 오류 값을 반환합니다. 여기서 3004 은 배열에 존재하지 않으므로 결과는#N/A입니다。
• ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE))=ISNA()#N/A):ISNA 함수는 값이 “#N/A” 오류인지 여부를 확인합니다. 그렇다면 함수는 TRUE 를 반환하고, “#N/A” 오류가 아닌 다른 값이라면 FALSE 를 반환합니다。 따라서 이 ISNA 수식은TRUE를 반환합니다。
• IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),「Missing」,「Found」) = IF(TRUE,「Missing」,「Found」):ISNA 및 VLOOKUP 함수의 비교 결과가 TRUE 이면 IF 함수는 Missing 을 반환하고, 그렇지 않으면 Found 를 반환합니다。 따라서 이 수식은Missing를 반환합니다。

COUNTIF 및 IF 를 사용하여 누락된 값 찾기

귀하의 목록에 있는 모든 제품이 공급업체 목록에 존재하는지 확인하려면 COUNTIF 및 IF 함수를 사용한 더 간단한 수식을 활용할 수 있습니다. 이 수식은 Excel 에서 0(0)을 제외한 모든 숫자를 TRUE 로 평가한다는 점을 이용합니다. 따라서 값이 다른 목록에 존재하면 COUNTIF 함수는 해당 목록에서 그 값이 나타나는 횟수를 반환하고, IF 는 이를 TRUE 로 간주합니다. 반면 값이 목록에 존재하지 않으면 COUNTIF 함수는 0 를 반환하며, IF 는 이를 FALSE 로 간주합니다。

일반 구문

=IF(COUNTIF())「lookup_range」,lookup_value),「Found」,「Missing」)

√ 참고： 「Found」， 「Missing」는 필요에 따라 원하는 값으로 변경할 수 있습니다。

귀하의 목록에 있는 모든 제품이 공급업체 목록에 존재하는지 확인하려면 아래 수식을 H6 셀에 복사하거나 입력한 후Enter키를 눌러 결과를 확인하세요。

=IF(COUNTIF())$B$6:$B$10,30002),「Found」,「Missing」)

또는 셀 참조를 사용하여 수식을 동적으로 만들 수 있습니다。

=IF(COUNTIF())$B$6:$B$10,G6),「Found」,「Missing」)

√ 참고： 위의 달러 기호($)는 절대 참조를 나타내며， 이는lookup_range가 수식을 다른 셀로 이동하거나 복사할 때 변경되지 않음을 의미합니다。 그러나lookup_value는 동적으로 유지되도록 하기 위해 달러 기호가 추가되지 않았습니다。 수식을 입력한 후 채우기 핸들을 아래로 드래그하여 아래 셀에도 수식을 적용하세요。

수식 설명

다음 수식을 예시로 사용하겠습니다。

=IF()COUNTIF($B$6:$B$10,G8),「Found」,「Missing」)

• COUNTIF($B$6:$B$10,G8):COUNTIF 함수는 G8 셀의 값인3004이 배열$B$6:$B$10에 몇 번 나타나는지 세어줍니다. 분명히 3004 은 배열에 존재하지 않으므로 결과는0입니다。
• IF()COUNTIF($B$6:$B$10,G8),「Found」,「Missing」) = IF(0,「Found」,「Missing」):IF 함수는 0 을 FALSE 로 평가합니다. 따라서 이 수식은 첫 번째 인수가 FALSE 일 때 반환되는 값인Missing를 반환합니다。

관련 함수

관련 수식