엑셀 조건에 맞는 값 모두 가져오기

엑셀에서 조건에 맞는 모든 값을 한 번에 가져오는 방법은 크게 FILTER 함수, INDEX + MATCH 중첩, 고급 필터 방식을 활용해 구현할 수 있습니다. 각 방법별 특징과 예제를 살펴보고, 실무에 맞는 최적의 방식을 선택해 보세요.

1. FILTER 함수 사용하기

Excel 365 이상 버전에서 사용할 수 있는 FILTER 함수는 지정한 조건을 만족하는 데이터 목록을 동적 배열로 반환합니다.

• 기본 구문 text=FILTER(배열, 조건_배열, [값_없음])
• 단일 조건 예제
  • 원본 데이터(A1:C10)에서 A열이 “서울”인 모든 행 추출 text=FILTER(A1:C10, A1:A10="서울", "결과 없음")
• 다중 조건 예제(AND)
  • A열이 “서울”이면서 B열이 “삼성”인 데이터 추출 text=FILTER(A1:C10, (A1:A10="서울")*(B1:B10="삼성"), "결과 없음")
• 다중 조건 예제(OR)
  • A열이 “서울”이거나 B열이 “삼성”인 데이터 추출 text=FILTER(A1:C10, (A1:A10="서울")+(B1:B10="삼성"), "결과 없음")

동적 배열을 지원하므로 조건을 바꾸면 결과가 자동 갱신되고, 추가 수식 없이 바로 사용 가능합니다.

2. INDEX + MATCH 중첩으로 다중 조건 처리하기

구버전 Excel(365 미만)이나 FILTER 함수를 지원하지 않을 때는 INDEX 함수와 MATCH 함수를 조합해 조건에 맞는 값을 순차적으로 추출할 수 있습니다.

1. 추출 대상 열을 INDEX의 첫 번째 인수로 지정하고 절대참조를 설정합니다.
2. MATCH 함수의 조건 범위와 찾을 값, 마지막 인수에 0(정확히 일치) 입력 후 중첩합니다.
3. 행 번호를 조정해 다음 값을 가져오려면 SMALL이나 ROW 함수를 추가로 활용합니다.

=INDEX($C$2:$C$100, 
  SMALL(
    IF(($A$2:$A$100=G1)*($B$2:$B$100=G2), ROW($A$2:$A$100)-ROW($A$2)+1),
    ROW(1:1)
  )
)

위 배열 수식은 A열이 G1, B열이 G2인 값 중 첫 번째 일치 항목을 C열에서 반환합니다. 추가 행 인덱스를 변경하면 두 번째, 세 번째 일치값을 순차적으로 가져올 수 있습니다.

3. 고급 필터(Advanced Filter) 활용하기

Excel의 고급 필터 기능은 별도 기준 영역(Criteria Range)을 설정해 다중 조건에 일치하는 데이터만 복사하거나 필터링할 수 있습니다.

• 절차
  • 기준 영역에 조건 필드와 조건 값을 입력
  • [데이터 탭 → 고급] 메뉴 실행
  • ‘원본 범위’, ‘조건 범위’, ‘복사 위치’ 지정 후 확인
• 예제
  • 직원 자료에서 부서가 “행정직”이고 급여가 3,300,000원 이상인 항목만 뽑아 다른 영역에 복사

고급 필터는 UI 기반으로 설정이 간편하며, 복사 위치 지정으로 추출 결과를 별도 시트에 저장할 수 있는 장점이 있습니다.

4. COUNTIFS 함수로 개수 세기

값 자체를 추출하는 것이 아니라 조건에 맞는 데이터의 개수를 파악할 때는 COUNTIFS 함수를 활용합니다.

=COUNTIFS(A2:A100, "서울", B2:B100, "삼성")  

위 식은 A열이 “서울”이고 B열이 “삼성”인 행의 개수를 반환합니다.

---

이처럼 Excel 버전과 목적에 따라 여러 방법을 선택할 수 있습니다. FILTER 함수는 간편하고 실시간 보고서에 적합하며, INDEX+MATCH 중첩은 구버전에서도 사용 가능합니다. 고급 필터는 별도 추출 시트가 필요할 때, COUNTIFS는 조건 만족 개수 집계에 최적화되어 있습니다. 필요에 따라 적절한 방식을 활용해 보세요.