업무 꿀팁브이룩업(VLOOKUP) 수식을 가로로 드래그할 때 오류 없이 적용하는 방법

브이룩업(VLOOKUP) 수식을 가로로 드래그할 때 오류 없이 적용하는 방법

실무에서 가장 자주 쓰이는 수식 중 하나는 단연 브이룩업입니다.
하지만 브이룩업은 기본적으로 ‘열(column)’ 기준으로 값을 찾아오는 함수이기 때문에
가로 방향으로 수식을 복사하거나 드래그하면 오류가 발생하기 쉽습니다.

예를 들어, 아래처럼 상품코드를 기준으로 우측 표에서 상품명, 소비자가, 판매가를 가져오는 경우
브이룩업 수식을 가로로 드래그하면 아래와 같은 오류가 발생합니다.

절대참조로 고정해도 해결되지 않는 문제

그럼 B3 셀을 절대참조($B$3)로 고정하면 어떻게 될까요? 행단위로 수식은 고정되지만,
모든 열에서 동일한 값만 참조하게 되어 원하지 않는 결과가 나타납니다.

해결방법: VLOOKUP+COLUMN 함수로 가로방향 인덱스 번호 자동증가 시키기

=VLOOKUP($B$3,$K:$U,COLUMN(A1)+2,FALSE)

$B$3 =검색할 값(절대참조로 지정)

$K:$U=값을 불러올 표 범위(절대참조로 지정)

COLUMN(A1)+2=표 범위의 첫번째 열(A1)에서 2번째 좌측에 있는값

FALSE=논리 정확히 일치하면 값 불러옴

수식을 위와 같이 입력해서 가로로 드래그하면 자동으로 표 범위의 열 범위에 맞춰 적용되는 것을 볼 수 있습니다

기존에 숫자가 들어가 있던 인덱스 순서 영역에 COLUMN 함수가 들어갔기 때문인데요 

A1은 첫 번째 인덱스 숫자로는 1을 의미하고 찾아올 값인 상품 명은 C1에 해당하기 때문에 COLUMN(A1)+2로 입력해주시면 C열에 해당하는 데이터를 찾아오고 드래그하면 자동으로 A1+4,A1+5,A1+6 이런식으로 인덱스 숫자가 늘어납니다 그래서 가로로 펼쳐진 행이 수십 수백개라고 해도 오류 없이 간편하게 일괄 적용 할 수 있는 꿀팁이라고 할 수 있죠

그럼 이제 마지막으로 검색할 값인 B3의 절대참조만 해제해주면 브이룩업을 어렵지 않게 전체 적용할 수 있습니다

엑셀 수식 일괄 수정하는 방법은?

CTRL+~ (컨트롤+물결표시) 를 누르면 현재 시트의 수식보기 화면으로 전환되는데요 여기서 찾기&바꾸기(CTRL+_F)로 $B$3를 B3로 절대참조를 해제해주시고 각 열별로 수식을 일괄 적용해 주시면 됩니다

이 방법은 브이룩업으로 찾을 값이 가로로 매우 길게 펼쳐져 있을 때 활용하기 좋은 방법입니다 업무에 도움되시길 바랍니다