VLOOKUP에 기반한 조건부 서식 지정 – Excel 및 Google 스프레드시트

이 튜토리얼에서는 Excel 및 Google 스프레드시트에서 VLOOKUP 함수의 결과를 기반으로 조건부 서식을 적용하는 방법에 대한 몇 가지 예제를 보여드립니다. 사용 중인 Excel 버전이 XLOOKUP을 지원하는 경우, XLOOKUP을 사용하는 것을 추천드립니다.

VLOOKUP 함수의 결과를 기반으로 조건부 서식을 적용하려는 예제를 살펴보겠습니다.

VLOOKUP 비교에 기반한 서식 지정

먼저, 각 학생의 성적(B-C 열)을 조회하여 점수가 72점 미만인 경우 빨간색 셀 채우기 색상을 적용하는 방법으로 이름 및 학생 ID 테이블(E-H 열)에 조건부 서식을 지정해 봅시다. 결과는 아래서 확인할 수 있지만 단계별로 설명해 드리겠습니다.

=vlookup($f3,$b$3:$c$7,2,false)<72

위의 수식을 조건부 서식으로 적용하는 방법을 살펴보겠습니다.

조건부 서식 적용

1. 조건부 서식을 적용할 범위를 선택합니다.

2. 리본에서 홈 탭 > 스타일 그룹 > 조건부 서식 > 새 규칙으로 이동합니다.

3. 팝업 메뉴에서 규칙 유형을 선택합니다: 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.

4. 수식 입력줄에 수식을 입력합니다.

5. 서식을 클릭하고 서식 설정 합니다. 이 경우 빨간색 채우기 색상을 선택합니다. 확인을 클릭합니다.

6. 이제 확인을 클릭하면 조건부 서식 지정 규칙이 적용됩니다.

이제 조건부 서식을 적용하는 방법을 알았으니 수식을 살펴봅시다.

조건부 서식 지정 수식을 셀에 입력하여 예상대로 작동하는지 테스트하는 것으로 시작하면 도움이 될 것 입니다. 아래에서 수식 먼저 수행해 봅시다.

VLOOKUP 함수

먼저, VLOOKUP을 수행합니다.

=VLOOKUP($F3,$B$3:$C$7,2,FALSE)

셀 참조를 잠그기 위해 $를 사용합니다. 이것은 모든 행에서 작동하는 수식을 만드는 데 필수적인 기호입니다.

논리적 비교

다음으로 결과를 설정된 기준과 비교해 봅시다.

=H3<72

조건부 서식은 TRUE인 모든 행의 셀 색상을 변경합니다.

모든 수식을 함께 결합하면 원래의 VLOOKUP 수식이 생성됩니다:

=VLOOKUP($F3,$B$3:$C$7,2,FALSE)<72

VLOOKUP이 공백인 경우 서식 지정

이전 예제에서 한 학생의 점수가 없는 것을 알 수 있습니다. ISBLANK 함수를 추가하여 이러한 셀을 감지하고 강조 표시할 수 있습니다:

=ISBLANK(VLOOKUP($F3,$B$3:$C$7,2,FALSE))

수식 결과는 다음과 같습니다:

VLOOKUP이 값 범위 내에 있는 경우 서식 지정

VLOOKUP의 또 다른 일반적인 시나리오는 값이 주어진 값 범위 내에 있는지 확인하는 것입니다.

이를 위해 VLOOKUP과 함께 AND 함수를 사용할 수 있습니다:

=AND(VLOOKUP($F3,$B$3:$C$7,2,FALSE)>=72,VLOOKUP($F3,$B$3:$C$7,2,FALSE)<=74)

공식을 살펴봅시다:

하한선

먼저, VLOOKUP의 출력이 주어진 하한 경계(예: 72)보다 크거나 같은지 확인합니다.

=H3>=72

상한선

다음으로, 같은 값이 상한 경계(예: 74)보다 작거나 같은지 확인합니다.

=H3<=74

AND 함수

마지막으로 AND 함수를 사용하여 두 조건이 모두 TRUE인지 확인합니다.

=AND(I3,J3)

위의 수식을 결합하면 원래 수식이 됩니다:

=AND(VLOOKUP($F3,$B$3:$C$7,2,FALSE)>=72,VLOOKUP($F3,$B$3:$C$7,2,FALSE)<=74)

조건부 서식 – 여러 VLOOKUP 조건

여러 조건부 서식 지정 규칙을 쉽게 추가할 수 있습니다:

조건부 서식 지정 규칙은 순서대로(위에서 아래로) 적용됩니다. True일 경우 중지 확인란을 선택하면 해당 조건이 충족될 경우 더 이상 서식 지정 규칙을 테스트하거나 적용하지 않습니다.

특정 예제에서 빈 VLOOKUP(예: 6행)은 빨간색(예: <72)과 주황색(예: ISBLANK)의 두 가지 조건을 충족합니다. ISBLANK 규칙이 먼저 적용되고 True일 경우 중지가 선택되어 있으므로 ISBLANK 조건이 충족되고 후속 규칙이 테스트되지 않으므로 6행이 주황색으로 강조 표시됩니다.

Google 스프레드시트에서 조회 결과에 기반한 조건부 서식 지정

위에서 설명한 모든 수식은 조회 테이블이 다른 시트에 있는 경우를 제외하고 Google 스프레드시트에서 동일한 방식으로 작동합니다. 이 경우 조건부 서식에서 다른 시트의 범위를 참조하려면 이름이 정의된 범위 또는 INDIRECT 함수를 사용해야 합니다.

=ISBLANK(VLOOKUP($C3,INDIRECT("Scores!B3:C7"),2,FALSE))

다음은 Google 스프레드시트에서 조건부 서식을 적용하는 방법에 대한 단계입니다:

1. 범위를 강조 표시한 다음 서식 탭 > 조건부 서식으로 이동합니다

2. 사이드바에서 “형식 규칙”으로 이동하여 “맞춤 수식”을 선택합니다

3. 수식 막대에 수식을 입력하고 서식 스타일을 설정한 다음 완료를 누릅니다: