■ 목차
1. 함수 설명
2. Lookup 함수의 구문 / 사용방법
3. Lookup 함수 사용해 보기 - 사원명으로 사원정보 찾아오기
4. Lookup 함수 응용하기 - 다중 조건 찾기 공식
** 핵심 요약
- Lookup 함수는 표나 테이블 같은 곳에서 원하는 값을 찾아오는 함수입니다.
- Vlookup, Hlookup 함수 유사하게 사용이 가능하지만 데이터가 항상 오름 차순으로 정렬이 되어야 있어야 합니다.
- 그렇기 때문에 Lookup 함수를 잘 사용하기 위해서는 원리와 규칙을 알고 사용할 것을 추천드립니다.
- 어떻게 사용할 수 있고 어떤 단점이 있는지 그리고 다중 조건 찾기 공식에 대해서 알아보도록 하겠습니다.
1. 함수 설명
- Lookup 함수는 lookup = 찾기, 검색하는 함수입니다.
- 정확히는 특정 행, 열에서 값을 찾아서 찾은 값의 위치를 기억하고 지정한 행, 열에서 동일한 위치의 값을 찾아 주는 함수입니다.
- 그래서 표와 같은 데이터에서 특정 사원의 입사일을 찾는다거나 특정 메뉴의 가격을 등을 찾을 수 있습니다.
- 그냥 보기에는 많이들 사용할 수 있는 함수 일 것 같으나 이게 데이터가 반드시 "오름 차순"으로 정렬이 되어야 하기 때문에
- 그렇게 생각보다 많이 사용을 하고 있지는 않는 것 같습니다.
- 데이터가 사전에 정렬되어야 한다는 부분만 아니며 굉장히 좋은 함수가 되었을 것 같은데 조금 아쉬운 부분이 있는 것 같습니다.
- 그렇기 때문에 Lookup 함수는 조심해서 잘 사용을 하셔야 하는 함수고 반드시 결괏값이 원하는 값이 되는지를 확인하시고 사용해 주시기 바랍니다.
- 이게 엑셀 도움말에는 벡터형, 배열형이라고 있는데 일단 "배열형"은 무시하시면 됩니다. 우리 보고 사용하라고 만든 함수가 아니고 다른 스프레드 시트 프로그램과의 호환용으로 제한적으로 사용이 가능한 기능입니다.
- 그리고 벡터형이라고 하는 것은 하나의 행 혹은 열에서 데이터를 찾고 동일한 크기의 지정한 행, 열에서 동일한 위치의 데이터를 찾는 일반적으로 우리가 사용하는 데이터 찾기에 해당하는 기능입니다.
- 벡터라고 하는 것이 한 방향을 의미하고 하나의 방향이라고 하는 것이 하나의 행 방향 혹은 열 방향을 의미한다고 이해하시면 됩니다.
- 반드시 기억하실 부분은
- ① 찾을 데이터가 있는 행, 열은 "오름 차순" 정렬되어야 합니다.
- ② 특정 행, 열에서 데이터를 찾아서 지정한 행, 열에서 데이터를 가져오는데 이 경우 찾기, 가져오기 데이터의 길이가 같아야 합니다.
- ③ 마지막으로 데이터를 찾아 주는 방식인데 정확하게 일치하는 값을 찾는 것이 아니라 "같거나 작은 값 중에서 최댓값"을 찾아 준다"입니다.
- 이 말은 일치되는 값이 있으면 "일치된 값"을 찾아 주지만 없으면 "가장 가까운 작은 값"을 찾아 준다는 의미이고
- 쉽게 얘기하자면 5000원으로 먹을 수 있는 가장 비싼 메뉴(같거나 작은 값 중에서 최댓값)를 찾을 수 있다는 의미입니다.
- 이게 어떻게 보면 기특하기도 하지만 "무조건 찾아 준다"라는 의미가 되고 = "오류가 없다" = "오류를 알 수 없다"라는 의미도 될 수 있습니다. ^^
- 그러한 의미로 Lookup 함수는 잘 사용을 하셔야 한다는 것입니다.
2. Lookup 함수의 구문 / 사용방법
- 아래 구문은 엑셀 도움말의 설명 내용입니다. 우리는 엑셀 함수의 구문을 다 외울 수도 없고 그럴 필요도 없습니다.
- 다만 엑셀 도움말과 함수사용 시 표시되는 풍선도움말의 용어들에 익숙해질 필요가 있기 때문에 기회가 될 때마다 편하게 봐주시기 바랍니다.
- LOOKUP(lookup_value, lookup_vector, [result_vector])
- lookup_value 필수 요소입니다. LOOKUP 함수를 사용하여 첫 번째 벡터에서 검색하려는 값입니다. lookup_value는 숫자, 텍스트, 논리값, 값을 참조하는 이름이나 값이 될 수 있습니다.
- lookup_vector 필수 요소입니다. 행이나 열을 한 개만 포함하는 범위입니다. lookup_value의 값은 텍스트, 숫자 또는 논리값이 될 수 있습니다.
- result_vector 선택 요소입니다. 행이나 열을 한 개만 포함하는 범위입니다. result_vector 인수는 lookup_vector와 크기가 같아야 합니다. 반드시 동일한 크기여야 합니다.
- 첫 번째 인수는 lookup_value입니다. 찾고자 하는(lookup) 값(value)으로 보통 숫자, 텍스트 등이 입력된 셀 주소를 지정합니다.
- 두 번째 인수는 lookup_vector인데 우리는 벡터형식으로 사용을 할 것이기 때문에 그냥 찾을 범위에 해당하는 셀 주소를 지정해 주시면 됩니다.
- 이때 셀 주소는 한 줄의 행방향 혹은 열 방향의 셀 주소를 지정해 주시기 바랍니다
- 그리고 lookup_vector 인수는 항상 "오름 차순(ㄱ→ ㅎ)으로 정렬이 되어 있어야 정확하게 원하는 값을 찾을 수 있습니다.
- 세 번째 인수는 result_vector로 찾고자 하는 값이 포함된 행 혹은 열 방향의 한 줄의 셀 주소을 지정해 줍니다.
- 그리고 세 번째 인수의 지정된 셀 주소의 개수는 두 번째 인수 lookup_vector와 항상 동일하게 지정해 주어야 합니다.
- 동일하지 않아도 값을 찾아 주는 경우도 있지만 찾는 값의 위치에 따라서 오류를 만들 수 있기 때문에 항상 동일한 길이로 지정해 줍니다.
- lookup_vactor에서 찾고자 하는 값(lookup_value)을 찾아서 위치를 기억하고 있다가
- result_vector에서 해당하는 위치의 값을 반환하는 방식으로 작동을 합니다.
- 그래서 Vlookup 함수처럼 찾고자 하는 열이 항상 우측에 있을 필요도 없고 심지어 행, 열을 다르게 셀 주소를 지정해 줘도 잘 찾아옵니다. ^^
- 원하는 값을 행 혹은 열에서 찾아서 몇 번째인지를 기억하고 있다가 지정한 행, 열에서 해당 위치의 값을 가져오는 방식이고
- ① 찾고자 하는 값이 있은 행, 열은 오름차순으로 정렬되어 있어야 하며
- ② 가져오고자 하는 값이 있는 행, 열은 Vlookup 함수와 다르게 어느 위치에 있던지 상관이 없습니다.
- ③ 일치하는 값이 없으면 크지 않는 값을 찾게 되며 그래서 왠만해서는 찾기 오류는 없지만 그래서 일치하는 값이 없다는 것도 알 수가 없습니다.
- 그냥 보기에는 아주 좋은 함수이지만 여러 가지 제한으로 사실 그렇게 많이 사용되지는 않습니다. ㅠㅠ
- Lookup 함수의 특성을 가장 잘 사용한 예제로는 "5000원"으로 구매할 수 있는 가장 비싼 메뉴를 찾기"로
- 오름 차순으로 정렬된 가격표에서 같거나 작은 가장 큰 값을 찾는 예제입니다. ^^
3. Lookup 함수 사용해 보기 - 사원명으로 사원정보 찾아오기
- Lookup 함수로 아래 "사원 정보" 데이블에서 "사원 ID", "소속"과 "입사일"을 구해보도록 하겠습니다.
- 1) 찾고자 하는 열 오름차순으로 정렬하기
- 가장 먼저 해주셔야 하는 일은 찾고자 하는 열 (사원명)를 "오름 차순"으로 정렬을 해야 합니다.
- Lookup 함수는 찾기 옵션(정확하게 일치하는 값 찾기, 유사한 값 찾기)이 없고 "유사한 값 찾기"로 작동을 하기 때문에
- 항상 순서대로 찾기를 하고 정렬 순서상 큰 값에 해당하면 직전 값을 반환해 버립니다. 그래서 항상 오름 차순 정렬이 필요합니다.
- 이 경우도 찾고자 하는 값 "윤서현"이 사원명의 첫 번째 값 "이서연"보다 오름차순 정렬상 먼저이기 때문에 찾는 값이 없다고 반환을 할 수 있기 때문에 먼저 정렬을 해줘야 합니다.
- 오름 차순 정렬은 "사원명(C2)"을 마우스로 선택을 한 후 리본 메뉴의 "텍스트 오름 차순 정렬" 기능을 사용해 주시면 됩니다.
- 2) 사원 ID 찾기, Lookup 함수 수식 입력 : " =LOOKUP(B10 , C3:C7, B3:B7) "
- ① 수식입력을 위한 등호 ( " = " )와 함수 및 괄호까지 입력을 합니다. : " = lookup ( "
- ② 첫 번째 인수 "lookup_value"인 찾고자 하는 값은 사원명 "윤서현"이 입력된 셀 (B10)를 마우스로 클릭으로 입력을 하고
- 인수 구분자인 콤마( " , " )를 키보드로 입력합니다. : " = lookup ( B10, "
- ③ 두 번째 인수 "lookup_vector"는 찾고자 하는 값이 있는 열로 "사원명"이 입력된 열로 "C3:C7"까지 마우스로 드래그해서 선택하여 입력하고 인수 구분자 콤마( " , " )까지 입력합니다. : " = lookup ( B10, C3:C7, "
- ④ 마지막 인수는 찾고 자는 값이 있는 "사원 ID"가 있는 열의 주소를 입력하는데 이때 두 번째 인수인 "lookup_vector"와 동일한 크기로 입력을 해줍니다. : " = lookup ( B10, C3:C7, B3:B7 "
- 두 번째 인수와 세 번째 인수는 동일한 크기로 입력을 해주어야 실수 없이 원하는 값을 찾을 수 있습니다.
- 이게 "윤서현"을 "사원명"에서 찾아보면 2번째에 "위치"하는 것을 알 수 있고 최종적으로 "사원 ID"열의 "2번째 위치"의 값을 가져오는 구조입니다.
- 그래서 같은 크기로 2, 3번째 인수를 입력해줘야 하고 테스트 결과로는 심지어 두 번째 인수는 열 방향, 세 번째 인수는 행 방향으로 지정을 해줘도 해당 위치의 값을 찾아오는 것으로 보입니다. ^^
- ⑤ 마지막으로 괄호 닫기( " ) ")을 입력하시고 "Enter"로 수식 입력을 완료합니다 : " = lookup ( B10, C3:C7, B3:B7 ) "
- 3) 소속, 입사일 찾기, Lookup 함수 수식 입력 : " =LOOKUP(B10 , C3:C7, E3:E7) " , " =LOOKUP(B10 , C3:C7, D3:D7) "
- 동일한 방식으로 "소속", "입사일"을 찾을 수 있습니다.
- 여기서 항상 주의하실 점은 두 번째 인수를 입력할 때 "제목"열이 포함되지 않게 셀 주소를 지정해 주셔야 합니다.
- Lookup 함수는 오름차순으로 정렬이 된 상태에서 사용을 하기 때문에 "제목" 열이 포함되면 이러한 "오름 차순 정렬"이 되지 않은 상태에서 찾기를 할 수 있기 때문입니다.
- 그리고 "입사일"의 경우 lookup 함수의 수식을 적용하면 이상한 숫자를 보여주는데 이는 "날짜를 나타내는 순차적인 일련번호"입니다. ㅠㅠ
- "날짜"의 원본 데이터는 숫자이고 이러한 숫자는 "1900년 1월 1일"이 "숫자 1"로 표현이 되고 이 날짜부터 하루에 숫자 1씩 더해지는 값을 가지고
- "날짜 서식"을 지정해 줘야 비로소 "1900-01-01"처럼 볼 수 있습니다.
- 그래서 "입사일"을 lookup 함수로 찾은 후에는 반드시 셀 서식에서 표시형식을 "날짜" 형식으로 변경해 주셔야 합니다.
4. Lookup 함수 응용하기 - 다중 조건 찾기 공식
- Lookup 함수를 사용해서 다중 조건 즉 2가지 이상의 조건을 동시에 만족하는 값을 찾는 방법은 공식처럼 이용을 할 수 있는데
- ① 배열 수식을 사용해줘야 하는데 Lookup 함수는 배열 수식 입력이 가능하기 때문에 별도로 "Ctrl + Shift + Enter"로 입력하실 필요가 없고
- 그래도 배열수식에 대한 기본적인 지식이 있으셔야 수식을 이해하고 사용이 가능할 듯합니다.
- ② Lookup 함수의 기본 사용은 "오름 차순 정렬"을 먼저 해줘야 하나 다중 조건 찾기 공식은 배열 수식으로 정렬 할 필요도 없습니다.
- "사원명"이 중복이 있는 경우에 "사원명" + "소속"으로 "사원 ID" 및 "입사일"을 찾는 문제입니다.
- 수식 : " =LOOKUP(1, (1/(C3:C7=B10)*1/(E3:E7=C10)), B3:B7) "
- 원리는 Lookup 함수가 오류값 ( "#DIV/0!" ) 값이 있는 경우에도 찾기가 가능한 부분과 배열 수식을 함께 사용한 수식입니다.
- =LOOKUP(1, (1/(C3:C7=B10)*1/(E3:E7=C10)), B3:B7)
- =LOOKUP(1, ({ #DIV/0! ; #DIV/0! ; 1 ; #DIV/0! ; 1 }*{ 1 ; #DIV/0! ; 1 ; #DIV/0! ; #DIV/0! }), B3:B7)
- =LOOKUP(1, { #DIV/0! ; #DIV/0! ; 1 ; #DIV/0! ; #DIV/0! }, B3:B7)
- 수식을 차근차근 뜯어보시기 바랍니다. ^^
** 핵심 요약 : Lookup 함수 사용법 - 다중 조건 찾기 및 주의 사항
1. 함수 설명
- Lookup 함수는 표나 테이블 같은 곳에서 원하는 값을 찾아오는 함수이다.
- 특정 행, 열에서 값을 찾아서 찾은 값의 위치를 기억하고 지정한 행, 열에서 동일한 위치의 값을 찾아 주는 함수이다.
- ① 찾을 데이터가 있는 행, 열은 "오름 차순" 정렬이 되어야 한다.
- ② 특정 행, 열에서 데이터를 찾아서 지정한 행, 열에서 데이터를 가져오는데 이 경우 찾기, 가져오기 데이터의 길이가 같아야 한다.
- ③ 데이터를 찾아 주는 방식은 정확하게 일치하는 값을 찾는 것이 아니라 "같거나 작은 값 중에서 최댓값"을 찾아 준다"이다.
- 즉, 일치되는 값이 있으면 "일치된 값"을 찾아 주지만 없으면 "가장 가까운 작은 값"을 찾아 준다.
- 그래서 "무조건 찾아 준다 = 오류가 없다 = 오류를 알 수 없다"
2. Lookup 함수의 구문 / 사용방법
- LOOKUP(lookup_value, lookup_vector, [result_vector])
- lookup_value 필수 요소입니다. LOOKUP 함수를 사용하여 첫 번째 벡터에서 검색하려는 값입니다. lookup_value는 숫자, 텍스트, 논리값, 값을 참조하는 이름이나 값이 될 수 있습니다.
- lookup_vector 필수 요소입니다. 행이나 열을 한 개만 포함하는 범위입니다. lookup_value의 값은 텍스트, 숫자 또는 논리값이 될 수 있습니다.
- result_vector 선택 요소입니다. 행이나 열을 한 개만 포함하는 범위입니다. result_vector 인수는 lookup_vector와 크기가 같아야 합니다. 반드시 동일한 크기여야 합니다.
- 첫 번째 인수는 lookup_value로 찾고자 하는(lookup) 값(value)으로 보통 숫자, 텍스트 등이 입력된 셀 주소를 지정한다.
- 두 번째 인수는 lookup_vector로 찾을 범위에 해당하는 셀 주소를 지정해 주면 된다.
- lookup_vector 인수는 항상 "오름 차순(ㄱ→ ㅎ)으로 정렬이 되어 있어야 정확하게 원하는 값을 찾을 수 있다.
- 세 번째 인수는 result_vector로 찾고자 하는 값이 포함된 행 혹은 열 방향의 한 줄의 셀 주소을 지정해 준다.
- lookup_vactor에서 찾고자 하는 값(lookup_value)을 찾아서 위치를 기억하고 있다가
- result_vector에서 해당하는 위치의 값을 반환하는 방식으로 작동한다.
3. Lookup 함수 사용해 보기 - 사원명으로 사원정보 찾아오기
- 1) 찾고자 하는 열 오름차순으로 정렬하기
- 2) 사원 ID 찾기, Lookup 함수 수식 입력 : " =LOOKUP(B10 , C3:C7, B3:B7) "
4. Lookup 함수 응용하기 - 다중 조건 찾기 공식
- ① 배열 수식을 사용해줘야 하는데 Lookup 함수는 배열 수식 입력이 가능하기 때문에 별도로 "Ctrl + Shift + Enter"로 입력할 필요가 없고
- ② Lookup 함수의 기본 사용은 "오름 차순 정렬"을 먼저 해줘야 하나 다중 조건 찾기 공식은 배열 수식으로 정렬 할 필요도 없다.
- 원리는 Lookup 함수가 오류값 ( "#DIV/0!" ) 값이 있는 경우에도 찾기가 가능한 부분과 배열 수식을 함께 사용한 수식이다.
- 수식 : " =LOOKUP(1, (1/(C3:C7=B10)*1/(E3:E7=C10)), B3:B7) "
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글