聰明不如鈍筆
총명불여둔필

엑셀에서 원하는 값 찾기(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() 함수를 알기 전후로 나뉜다고 생각합니다. 첫 번째 이유는 물론 실제로 활용할 일이 많기 때문이고, 두 번째는 다른 기능으로 가는 관문이라고 믿기 때문입니다. 일단 이 함수를 어떻게 쓴다는 개념을 이해하셔야 다음 단계로 넘어가기가 수월합니다. 아직 이 세 함수를 어떻게 쓰는지 모르고 계셨다면 이 포스트가 도움이 되었기를 바랍니다.

댓글, 36

  • 댓글 수정/삭제 닥슈나이더
    2018.09.18 10:27

    vlookup에 true false 설명과 예시가 달라요....
    전 false 성애자...ㅠㅠ

  • 댓글 수정/삭제 힘내요
    2018.10.12 09:08

    감사합니다. 덕분에 엑셀작업 하는데 시간이 많이 줄었어요 ♥

  • 댓글 수정/삭제 어렵다..
    2019.02.26 09:44

    그럼 반대로 16년도에 타율이 0.293인 회사는 어떻게 찾나요??? ㅠㅠ

  • 댓글 수정/삭제 네오에이치
    2019.04.17 18:53

    정말 많은 도움이 되었습니다. 담아갈께요 ^^

  • 댓글 수정/삭제
    2019.12.07 01:41

    비밀댓글입니다

  • 댓글 수정/삭제 문성현
    2020.02.08 12:26

    찾을려는값이 101~150사이에있는값이라면 셀에 125를 찾을려고적었는데 찾을려는부분이 101~150으로 되어있다면 어떻게해야되나요?

  • 댓글 수정/삭제 문성현
    2020.02.09 16:27

    여러가지가있어서요 101~150사이 151~200사이 이렇게되어있어서

  • 댓글 수정/삭제 선생님
    2020.03.27 13:55

    감사합니다!
    질문있슴다!
    다중조건경우에서
    예시와는 역으로
    값으로 행의 값, 열의 값을 찾을려고한다면 어떻게하죠?

    ex) "0.291"입력 시 "넥센"과 "2016"을 출력

    +1
    선생님께서 match 구현 시 A열, B열 따로 입력해주고(+배열활용) 값을 추출하였습니다.

    제가 원하는 구현은
    전체범위에서 match로 구현 전, 전체 열 중 일치 경우를 하나하나 찾고(if) 그 경우에 대해서만 index+match를 구현해야하나요?

    ldh45322@쥐메일

    •  수정/삭제 kini
      2020.03.27 14:27 신고

      궁금해 하시는 건 vlookup()을 두 번 쓰시면 되지 않나요?
      (이 자료는 같은 값이 많아서 실제로는 정확하게 안 되겠지만) 값을 기준으로 연도 한 번, 팀 한번 가져오라고 쓰시면 될 듯한데요?

    •  수정/삭제 선생님
      2020.03.31 19:12

      앗 ㅋㅋ 답변감사드려욤 ㅋㅋ
      예시를 잘못 들엇네욤!
      다중조건 경우가 아니라

      연도 두산 롯데 삼성 kia....
      2015 0.29 0.28 0.30 0.25
      2016 0.26 0.22 "0.31" 0.23
      2017 0.46 0.22 0.61 0.27

      이렇게 있을 때 말입니다. ㅎㅎ
      (중복은 일단 제외..)

      ex) "0.31"입력 시 "삼성"과 "2016"을 출력

      ++ㅎㅎ 제머리론 한번에 함수를 만들기가 구현 되지 않넹용ㅋㅋ
      1 연도 또는 팀마다 match가 되는지 찾고
      2 match 결과가 존재가 있다면 그 때야 행과 열 인덱스정보를 돌려줄수 있을 것 같네요.

      +++감사합니다!

    •  수정/삭제 kini
      2020.04.03 12:55 신고

      아, 이때는 iserror() 함수를 쓰시면 될 듯합니다.

  • 댓글 수정/삭제 선생님
    2020.04.06 11:01

    감사합니다. iserror 쓴다는 것은 에러 발생 시에는 계산 안하도록 하는 것이겠네요?!
    행 또는 열마다 에러발생 여부 평가가 필요할테니, 한 셀 내에서 모든 함수를 나타내는 것은 무리겠네요?!?! ㅎㅎ

  • 댓글 수정/삭제 선생님
    2020.04.09 08:02

    한 줄 (= 한 행 또는 열)! yes sir ! 캄사드립니다 선생님! 좋은하루되십셔!

  • 댓글 수정/삭제 다현
    2020.05.17 16:10

    랜덤한 점수를 제시해주고 점수가 60~69면 D학점 70~79면 C학점 등과 같이 범위를 주고 학점을 자동으로 구할 수 있게 하려면 어떻게 해야할까요 ㅜ

    •  수정/삭제 kini
      2020.05.17 21:02 신고

      첫 예제에서 E열을 점수 범위, F열을 학점으로 바꾸시면 금방 해결하실 듯한데요?

  • 댓글 수정/삭제 정리
    2020.06.26 10:30

    문의드려요. 같은 행에서 두개 중에 큰값을 찾아 보여주는 수식이 있을까요?

    •  수정/삭제 kini
      2020.06.28 22:47 신고

      A1, B1에 숫자가 있다면 C1에 =MAX(A1:B1)라고 입력하시면 더 큰 숫자가 나옵니다.

  • 댓글 수정/삭제 JHR
    2020.08.11 13:17

    Index, Match 함수 자체는 알고 있었는데 배열함수를 이용한 다중 조건도 가능했군요.
    무척 유용한 정보라고 생각합니다. 계속 써서 익숙해져봐야겠네요~. 감사합니다.^^

  • 댓글 수정/삭제 NEAL
    2020.09.29 14:05

    위 "선생님"의 질문과 유사한 내용입니다만, 좀 달라서 질문드립니다.
    중복되는 특정값을 행렬 내에서 찾을 경우에는 어떻게 해야될까요?

    제가 찾으려는 값은 a1에 있는 "홍"이고,
    a2:d999 안에는 "홍"이 세개 있습니다.
    a1값이 해당 영역 안에 있다/없다만 판별하면 되는건데..
    구현이 좀 어렵네요 ㅠ

    범위가 약 100행 25열 정도라서...index area와 match를 사용하기엔 범위지정할게 너무 많아지구요...윗분처럼 match를 각 열별로 반복하는것도 작업량이 쓸데없이 많아지는 느낌이라 질문드립니다 ㅠㅠ

    (추가)생각해보니 행렬안 유무를 판단하는건 그냥 countifs로 해결이 가능하네요^^

    참조관련 질문은 비밀글로 다시 올리겠습니다 유익한 내용 감사드립니다!

  • 댓글 수정/삭제 초보빨무1vs1
    2020.10.30 16:19

    선생님, 감사합니다. 다른게 아니라, 혹시 엑셀에서 두개의 시트A, B에서 두 시트의 C열이 순서가 뒤바뀌었을 뿐 같은 값들을 가지고 있다고 할 때, 시트 A의 C열과 B의 C열을 서로 비교하여 시트 B의 C열의 특정 행을 시트 A로 끌어올 수 있는 방법은 없을까요?

    •  수정/삭제 kini
      2020.11.03 10:49 신고

      그 작업을 어떻게 진행하는지 소개해드린 게 이 포스트인데 말입니다…

  • 댓글 수정/삭제 초보빨무1vs1
    2020.10.30 16:23

    예를 들면 이런거죠. 시트 A의 C열이 순차적으로 미국, 한국, 독일, 프랑스, 인도 라는 값을 가지고 있고, 시트 B의 C열이 프랑스, 인도, 독일, 한국, 미국의 순서로 값을 가지고 있다고 하죠. B의 D열은 순서대로 1, 2, 3, 4, 5라는 값을 가지고 있구요.
    이걸 시트 A에서 자동적으로 시트 B와 비교하여 시트 A의 D열을 5, 4, 3, 1, 2로 자동적으로 끌어올 수 있게끔 할 수 있는 방법을 찾고 있습니다. 조언을 구해도 될까요?

    •  수정/삭제 kini
      2020.11.03 10:50 신고

      시트 A에서 자료를 가져오고자 하시는 셀(여기서는 D1)에

      =VLOOKUP(C1,SheetB!$C$1:$D$5,2,0)

      이렇게 쓰시고 D2:D5에 붙여 넣으시면 원하시는 결과를 얻으실 수 있을 겁니다.

  • 댓글 수정/삭제 초보빨무1vs1
    2020.11.09 07:56

    선생님, 알기 쉬운 답변 정말 감사드립니다!

account_circle
vpn_key
web

security

mode_edit
Kidult/Excelsior | 카테고리 다른 글 더 보기