엑셀함수정리, VLOOKUP 함수 HLOOKUP 함수로 엑셀 원하는 값 찾기

VLOOKUP과 HLOOKUP 함수

*V=vertical 수직, 세로

*H=horizontal 수평, 가로

 

VLOOKUP 함수

=VLOOKUP(찾는 값, 참조할 범위, 순번, 선택 : TRUE or FALSE)

=VLOOKUP(찾으려는 값, 찾으려는 값이 있는 범위, 얻고자하는 값의 열(세로줄) 순번, 선택 : 유사한 값 or 정확한 값)

 

* TRUE (또는 1) = 유사 일치
  FALSE(또는 0) = 정확히 일치

 

▼엑셀 vlookup 함수 안되는 이유

VLOOKUP 함수의 찾을값은 반드시 범위의 맨 왼쪽(첫번째 열)에 있어야 한다. 다시말해, VLOOKUP 함수는 찾을값의 오른쪽방향으로만 값을 조회할 수 있다. VLOOKUP의 3번째의 순번에 -1 안 됨. 왼쪽 안 됨.

찾는 값이 첫번째에 안 있고 2번째에 있을 때

위 사진은 초록색으로 칠해진 이름 '이모모'만 입력하고 나머지 부서, 직책, 이메일은 VLOOKUP 함수를 썼는데, 찾는 값이 범위의 맨 왼쪽에 있지 않아 값들이 다 틀리게 나왔다.

 

내선번호는 수기로 입력하고 그에 따른 부서, 이름, 직책, 이메일은 자동으로 값이 불러지게끔 VLOOKUP 함수를 사용하였다.

 

VLOOKUP의 4번째 칸에는 아무것도 입력하지 않거나, TURE(유사 일치)를 입력하면 잘못된 값이 나왔다. FALSE(정확히 일치)를 입력해야 제대로 된 값이 나온다.

 

부서를 자동으로 부르는 함수에

첫번째는 찾는 값이고,

두번째는 범위이며,

세번째는 불러올 값의 열 번호(세로줄, 범위 안에서 순번을 센다.),

네번째는 FALSE(정확히 일치)를 적었다.

 

이후 이름, 직책, 이메일은 세번째 칸에 각각 3, 4, 5를 적은 거 외에는

부서와 첫번째 칸, 두번째 칸, 네번째 칸이 동일하다.

 

이와 같이 찾는 값, 범위, 유사/정확히 일치가 같다면

F4로 찾는 값과 범위를 고정해놓고 '자동채우기'를 하면 드래그하여 복사할 수 있다.

단 세번째 칸인 열 번호는 똑같기 때문에 열 번호는 수기로 바꿔줘야한다.

 

혹은 그냥 Ctrl+C, Ctrl+V해서 복사 붙여넣기해도 된다.

달러($) 표시로 잠근 후 드래그한 모습 예시.

HLOOKUP 함수

=VLOOKUP(찾는 값, 참조할 범위, 순번, 선택 : TRUE or FALSE )

=VLOOKUP(찾으려는 값, 찾으려는 값이 있는 범위, 얻고자하는 값의 행(가로줄) 순번, 선택 : 유사한 값TRUE or 정확한 값FALSE)

 

VLOOKUP 할 때랑 똑같이 찾는 값, 범위, (이번엔 행) 순번, 정확히 일치를 적으면 된다.

팁★범위 이름정의하기

위에서는 범위 고정시 달러($)표시를 하라고 했지만 달러($)가 붙으면 길어져 자신/다른 사람이 보기에 한눈에 파악하기 힘들 수 있다.

그럴 때는 이름 정의를 활용하면 된다.

범위를 드래그하여 잡아준 후 위 셀 이름 영역에 이름을 정의하면 된다. 단 띄어쓰기는 안 된다.

찾는 값을 달러($)표시 한 번으로 고정해주고 범위를 아까 설정한 '룩업범위'라는 이름을 한글로 적었더니 자동으로 빨간 글씨가 되며 범위를 잡아준다.

 

참고 : 선택하여 붙여넣기-행/열 바꿈 기능

참고로 나는 포스팅하기 위하여 선택하여 붙여넣기-행/열 바꿈을 사용하였다.

Ctrl+C하여 복사한 후 첫번째 셀에 마우스 오른쪽 클릭하고 선택하여 붙여넣기-선택하여 붙여넣기로 들어갔다.

선택하여 붙여넣기-행/열 바꿈