엑셀 가로와 세로의 특정 조건을 갖는 값 찾기

엑셀을 사용하다보면 행과 열의 조건을 맞는 값을 찾아야할 경우가 있습니다.

엑셀 가로세로 조건으로 값 찾기

오늘은 엑셀의 가로와 세로의 조건으로 값을 찾는 방법을 알아보겠습니다.

 

먼저 일반적으로 우리가 하나의 조건을 가지고 찾는 것은 Vlookup함수나 Hlookup함수를 통해서 

 

값을 찾을 수 있습니다.

 

하지만 복수의 조건으로 값을 찾아야 한다면 다른 방법을 사용하는게 편리합니다.

 

엑셀 가로 세로 조건으로 값 찾기

먼저 아래와 같이 연도별로 시험 성적이 있는 데이터를 생각해 봅니다.

 

이 때 우리는 2021년에 영어 점수를 찾고 싶은데요.

이 때 함수를 활용하기 위해서는 값의 배열을 정리해보아야 합니다.

• 가로조건은 B1~D1까지의 조건이고
• 세로조건은 A2~A4까지
• 찾을 범위는 B2~D4까지임을 알 수 있습니다

조건

이를 기반으로 함수를 짜보면 아래와 같이 INDEX함수와 Match함수를 활용해서 값을 구할 수 있는데요.

 

각각의 값을 자세히 알아보도록 하겠습니다.

즉 함수는 아래와 같은 구조로 이루어져 있습니다.

각각의 개별 함수를 아래에서 보다 자세하게 살펴보겠습니다. 

 

함수별 설명

가장 먼저 INDEX함수는 MS 공식홈페이지에서 자세한 내용을 알 수 있는데요.

INDEX 함수

기본적으로 INDEX 함수는 테이블이나 범위에서 값 또는 값에 대한 참조를 반환합니다.

 

즉 INDEX(array, row_num, [column_num]) 의 구조로 작성되고,

 

(찾고자 하는 배열, 행의 위치, 열의 위치)의 구조입니다.

 

작성한 함수를 살펴보면 INDEX(B2:D4  => 전체 찾을 범위는 B2:D4에 위치해 있다.

 

그리고 이어서 행의 위치는 MATCH(G2,A2:A4,0)로 사용하였는데요.

 

MATCH함수 역시 MS의 공식 홈페이지에서 상세 사용법을 알 수 있습니다.

MATCH 함수

MATCH 함수는 셀 범위에서 지정된 항목을 검색하고 범위에서 해당 항목이 차지하는 상대 위치를 반환하는데요.

 

기본 문법은 찾으려는 값, 범위, 타입입니다.

MATCH(G2,A2:A4,0) => G2의 값을 A2:A4에서 몇번째인지 찾아주는 것인데요.

                                         이 역시 2번째에 있으니 값은 2가 반환됩니다.

 

MATCH(H1,B1:D1,0) =>  H1의 값이 B1:D1중에서 몇번째인지 표시해달라는 것인데요.

                                        2번째에 있으니 값은 2가 반환됩니다.

이렇게 함수의 개별값들을 알아보았는데요.

 

실제로 함수를 확장해서 사용하기 위해서는 $를 통해 절대값을 입혀야지 복사할때 범위가 고정되는데요.

 

범위는 항상 고정이기 때문에 세로가로에 다 $를 넣어 고정시켜야합니다.

 

MATCH함수에는 범위는 고정이나 찾는 값은 G열은 고정이나 행은 변경되어야 합니다.

 

$G2로 지정합니다. 다음에 나오는 H$1은 반대로 열이 변경되어야 하고, 행은 1행으로 고정이니 반대로 입력합니다.

 

마지막으로 이제 자동 채우기 기능을 통해서 엑셀에서 가로세로 조건에 따라 원하는 값을 불러오도록 만들 수 있습니다.

한번 천천히 따라해보시면 좋을 것 같습니다.

 

혹시 따라하기가 어렵다면 아래 실습파일을 가지고 테스트 해보시기 바랍니다.

 

그럼 즐거운 하루 보내세요

 

엑셀_가로세로실습.xlsx

0.01MB