엑셀에서 원하는 값 찾기(feat. VLOOKUP, INDEX&MATCH)

• 이 글이 필요한 분: 엑셀을 표+계산기로만 쓰고 계신 분

• 이 글을 읽고 나면 할 수 있는 일: 곳곳에 흩어진 데이터를 한 군데 모아서 정리할 수 있다. 예) 프로야구 팀별 연도별 타율 정리 

다음은 한 지인께서 저희가 자주 가는 인터넷 사이트에 '엑셀 질문입니다'하고 올리신 글입니다.

시트 1 

        A열        /    B열 

5,000~6,000㎡ / 100,000원 

6,000~7,000㎡ / 200,000원 

7,000~8,000㎡ / 300,000원 

시트2 

  A열 /  B열      /  C열 

A업체 / 5,230㎡  / 100,000원 

B업체 / 6,587㎡  / 200,000원 

위 자료가 있습니다. 시트1에 해당되는 면적에 대한 금액(B열)을 시트2에서 C열에 나타나고 싶은데 함수로 어떻게 할까요??? 

알려주세요~~~ 

안되면 전 하나 하나 다 입력해야 합니다. ㅠㅠ 도와주세요 ㅠㅠ

마이크로소프트(MS) 엑셀에서 이렇게 한 표에서 다른 표로 자료를 가져오고 싶을 때 도움을 받을 수 있는 함수가 바로 VLOOKUP()입니다. VLOOKP에서 V는 수직(Vertical)이라는 뜻. 그러니까 세로(행)를 기준으로 필요한 값을 찾아오라고 엑셀에 명령하는 함수가 바로 VLOOKUP()이라고 할 수 있습니다.

엑셀 검색 기본 VLOOKUP()

VLOOKUP() 함수를 쓸 때는 기본적으로 아래 같은 내용이 들어가야 합니다.

=VLOOKUP(검색 기준값, 검색 대상 범위, 찾으려는 값이 들어있는 열, 정확한 일치 여부)

무슨 말씀인지 잘 모르시는 게 당연한 일입니다. 이제부터 실제 사례를 가지고 한번 차근차근 살펴 보겠습니다.

지인께서 올리신 자료에 문제가 있다면 엑셀은 범위와 단위를 이해하지 못한다는 점입니다. 그래서 아래 그림처럼 데이터를 손질해야 합니다.

여기서 우리가 검색 기준으로 삼으려는 값은 B열에 들어 있습니다. 검색 대상 범위는 E1:F4 열에 들어 있습니다. 그리고 우리가 실제로 가져오고 싶은 값이 있는 건 F열(두 번째 B열) = 검색 대상 가운데 두 번째 열입니다. 따라서 C2 셀에 들어갈 함수식은 이렇게 쓸 수 있습니다.

C3에 들어갈 함수식은 그럼 어떻게 될까요? 맨 앞에 있는 B2만 B3로 바꾸면 됩니다. 그런데 만약 그냥 Ctrl+C/V 하시면 함수식이 이렇게 바뀝니다.

=VLOOKUP(B3, E2:F5, 2)

검색 대상 범위가 E2:F5로 달라지는 겁니다. 엑셀에서는 이를 막으려고 '절대참조'라는 개념을 도입하고 있습니다. C2에 처음부터 이렇게 입력했다면 함수식을 복사해도 검색 대상이 바뀌지 않습니다.

=VLOOKUP(B2, $E$1:$F$4, 2)

이때 $는 직접 입력하셔도 좋지만 F4 키를 활용하셔도 됩니다. E1에 마우스 커서를 가져가시고 F4 키를 한 번 누를 때마다 이 셀 주소가 E1 → $E$1 → E$1→ $E1 형태로 바뀝니다.

$E$1은 어느 방향으로 복사해도 이 주소를 바꾸지 말라는 뜻입니다. E$1은 행을 바꾸지 말라는 의미. 이번에는 아래로 복사를 하는 케이스니까 사실 E$1:F$4로만 바꿔도 충분합니다. 마찬가지로 $E1은 열을 따라 옆으로 복사할 때 E열을 그대로 두라는 의미입니다.

여기까지 입력을 마치셨으면 그냥 C2에 입력한 함수식을 C3, C4에 복사하시면 아래 그림처럼 우리가 원하는 결과가 나온다는 걸 알 수 있습니다.

이때 주의하셔야 할 건 검색 대상 범위에서 △우리가 찾고자 하는 값이 맨 왼쪽에 있어야 하고 △이 값이 오름차순 정렬 상태여여 한다는 점입니다. 지금은 이 조건을 만족하고 있는 상태입니다.

신경 써서 읽고 계신 분은 분명 위에서는 VLOOKUP() 함수 끝에 정확하게 일치하는지 쓴다고 했는데 여태 한 번도 쓰지 않았다는 걸 눈치채셨을지 모릅니다. 그건 기본값이 '유사 일치'로 되어 있기 때문입니다. 

정확하게 일치하는 값만 찾고 싶으실 때는 아래처럼 맨 끝에 FALSE를 더하시면 됩니다.

=VLOOKUP(B2, $E$1:$F$4, 2, FALSE)

그러면 C2, C3 셀에는 해당하는 값을 찾을 수 없다는 뜻에서 #N/A 에러가 나오게 됩니다. 7000은 정확한 값이 있기 때문에 300000이라는 값을 가져왔습니다.

함수식 끝에 FALSE 대신 0을 써도 정확하게 일치하는 값만 뽑아낼 수 있습니다. 저는 보통 이 방식을 씁니다.

만약 우리가 찾고자 하는 옆으로 넓게 퍼져 있을 때는 어떻게 해야 할까요? 이럴 때는 수평(Horizonal)에서 첫 글자를 따온 HLOOKUP() 함수를 활용하시면 됩니다. 이렇게 말입니다.

이번에도 HLOOKUP()은 비슷한 값이 있으면 찾아옵니다. 정확한 값만 찾아오게 하려면? VLOOKUP()처럼 맨 끝에 TRUE 또는 0을 붙이면 됩니다.

가로, 세로 다 찾아야 할 땐 INDEX + MATCH

위에서 본 데이터는 세로 또는 가로 한 방야향에서만 데이터를 찾으면 그만이지만 두 방향에서 모두 찾아야 할 때도 있습니다. 그리고 어쩌면 데이터를 이런 식으로 정리할 때가 더 많은지도 모릅니다. 아래 있는 2015~2017년 프로야구 팀 타율 데이터처럼 말입니다.

만약 이 자료에서 2016년 넥센 팀 타율을 뽑아내고 싶다면 어떻게 하면 될까요? 이를 엑셀이 이해하도록 하려면 세 번째 줄에 있는 아홉 번째 열을 골라서 내용을 가져오라고 해야 합니다. 이럴 때는 함수 두 개가 필요합니다. 바로 INDEX()와 MATCH()입니다.

INDEX() 함수 표현식은 아래처럼 아주 간단합니다.

=INDEX(검색 범위, 행, 열)

이 팀 타율 데이터는 A1부터 K4까지 자리잡고 있습니다. 우리는 세 번째 행, 아홉 번째 열을 알고 싶으니까 =INDEX(A1:K4, 3, 9)라고 쓰면 0.293이라는 값을 가져옵니다.

그런데 이렇게 일일이 범위를 정해주는 건 효율이 너무 떨어집니다. 차라리 그냥 직접 복사하는 게 나을지도 모릅니다. 그래서 MATCH() 함수가 우리를 기다리고 있습니다. MATCH()는 가로냐 세로냐 따지지 않고 우리가 찾으려는 값이 몇 번째 위치에 있는지 알려줍니다.

=MATCH(찾을 값, 검색 범위)

예컨대 지금 자료에서는 먼저 2016년이 필요한 거니까 연도가 들어 있는 A열에서 2016년을 찾습니다.

=MATCH(2016, A1:A4)

넥센을 고를 때는 옆으로 가면 됩니다.

=MATCH("넥센", A1:K1)

당연히 이 값을 직접 입력해야 하는 건 아닙니다. 필요한 값이 다른 셀에 들어 있다면 그 셀을 지정하는 방식으로도 검색 조건을 지정할 수 있습니다. 아래 그림에서는 연도가 C10, 팀이 C11에 들어 있기 때문에 이 값을 넣었습니다.

데이터가 들어 있는 행과 열을 모두 알게 됐으면 INDEX()에 한꺼번에 묶어 쓰시면 됩니다.

=INDEX(A1:K4, MATCH(C10, A1:A4), MATCH(C11, A1:K1))

이렇게 입력을 마치고 나시면 0.293이라는 값이 잘 나왔을 겁니다.

이렇게 INDEX()와 MATCH()는 서로가 서로를 도와서 단순 반복 업무로부터 우리는 해방시켜주는 고마운 친구입니다. 혹시 사용법을 모르셨다면 이번 기회에 꼭 익혀두세요.

다중조건일 땐 {INDEX + MATCH}

그러나 세상은 그렇게 간단하지가 않습니다. 때로는 데이터가 아래처럼 세로 기준으로 (예쁘게) 정렬한 상태일 수도 있습니다. 

이 때는 2016년에는 팀이 10개 있고, 넥센도 2016년과 2017년 두 가지 값이 있습니다. 이번에도 2016년 넥센 팀 타율을 구한다면 2016년도 만족하고, 넥센도 만족하는 두 가지 조건을 모두 고려해야 합니다. 이럴 때는 흔히 다중조건 검색이라고 표현합니다.

정답부터 말씀드리면 이렇게 쓰면 됩니다.

{=MATCH(1,(범위1=조건1)*(범위2=조건2),0)}

갑자기 { }가 등장했습니다. 이 때 { }는 배열이라는 뜻입니다. MS 오피스 홈페이지는 엑셀에서는 배열을 "갑 행, 값 열 또는 값 행 및 열의 조합"으로 간주하며, 배열 수식은 "배열에 있는 하나 이상의 항목에서 여러 계산을 수행할 수 있는 수식"이라고 설명합니다. 그냥 A열 전체에서 2016을 골라내고, B열 전체에서 넥센을 골라내는 계산을 수행해야 하기 때문에 배열을 쓴다고 이해하시면 됩니다.

여기서 주의해야 할 건 저 { }는 직접 입력하시는 게 아니라는 점입니다. 함수식을 입력창에 다 쓰신 다음에  Ctrl + Shift + Enter 키를 동시에  누르시면 자동으로 함수식 앞뒤에 { }가 붙고, 그다음에야 계산 결과를 출력합니다.

위에 있는 함수식에서 범위를 1로 지정한 건 뒤에서 범위를 따로 지정할 것이기 때문이고, 맨 끝에 나온 0은 (당연히) 정확한 값을 찾으라는 뜻입니다. 

참고로 MATCH()에서는 뒤에 붙는 검색 조건이 VLOOKUP()하고는 다릅니다. 0은 똑같은 값을 찾으라는 뜻으로 똑같지만 크고 작은 값을 분류합니다. 더 작은 값을 찾고 싶을 때는 1, 더 큰 값을 찾고 싶을 때는 -1을 쓰셔야 합니다. 

우리는 2016년 넥센 팀 타율을 구하고 있기 때문에 이렇게 쓰면 원하는 값을 얻을 수 있습니다. F3에 연도, F4에 팀이 들어 있는 상태입니다.

{=MATCH(1,(A1:A21=F3)*(B1:B21=F4),0)}

이러면 2를 출력합니다. 실제로 두 번째 줄이 2016년 넥센 기록입니다. 우리가 찾으려는 타율은 세 번째 열에 있으니까 이렇게 쓰면 됩니다.

=INDEX(A1:C21, F6, 3)

물론 MATCH()를 INDEX() 안에 포함해 한꺼번에 묶어서 쓰셔도 됩니다. 이렇게 말입니다. 

{=INDEX(A1:C21, MATCH(1, (A1:A21=F3)*(B1:B21=F4), 0), 3)

노파심에 한 번 더 말씀드리면 저 { }는 직접 입력하시는 게 아닙니다. Ctrl + Shift + Enter 키를 동시에 누르시면 자동으로 배열 수식으로 바뀌는 겁니다.

배열이 너무 어렵다고 느끼신다면 INDEX()를 한 번 더 활용하는 방법도 있습니다.

=MATCH(1, INDEX((A1:A21=2016)*(B1:B21="넥센"), 0, 1), 0)

이렇게 쓰시면 2를 출력합니다. 위에서 보신 것처럼 우리가 찾는 자료가 두 번째 행에 있기 때문입니다. 다시 처음부터 묵으면 이렇게 쓸 수 있습니다.

=INDEX(A1:C21, MATCH(1, INDEX((A1:A21=2016)*(B1:B21="넥센"), 0, 1), 0), 3)

그냥 Ctrl + Shift + Enter 키를 한 번 동시에 누르시는 게 훨씬 편하다고 생각하지 않으십니까?

똑같은 방법으로 직접 실습을 해보고 싶으신 분은 아래 있는 엑셀 파일을 내려받으시면 됩니다. 2016, 2017년 프로야구 10개 팀 타율 자료입니다.

avg.xlsx

개인적으로 엑셀은 VLOOKUP() 그리고 INDEX(), MATCH() 함수를 알기 전후로 나뉜다고 생각합니다. 첫 번째 이유는 물론 실제로 활용할 일이 많기 때문이고, 두 번째는 다른 기능으로 가는 관문이라고 믿기 때문입니다. 일단 이 함수를 어떻게 쓴다는 개념을 이해하셔야 다음 단계로 넘어가기가 수월합니다. 아직 이 세 함수를 어떻게 쓰는지 모르고 계셨다면 이 포스트가 도움이 되었기를 바랍니다.