엑셀 vlookup 행고정 제대로 하는 방법

얼마 전 자동차 부품 재고표를 손보다가, 첫 줄에서는 단가가 잘 나오는데 수식을 아래로 복사하자 값이 줄줄이 틀어지는 경우를 봤습니다. VLOOKUP 수식에서 조회표 범위가 함께 내려간 게 원인이었죠. 이런 상황에서 흔히 말하는 ‘행고정’은 어느 셀을 고정할지부터 구분해야 제대로 해결됩니다.

수식을 복사할 때 범위가 왜 움직일까

엑셀에서 A2나 G2:I6처럼 달러 기호가 없는 참조는 상대참조입니다. 수식을 한 행 아래로 복사하면 A2는 A3으로, G2:I6은 G3:I7로 바뀝니다. 부품코드를 찾는 셀은 다음 행으로 이동해야 하지만, 기준이 되는 부품 단가표는 같은 자리에 있어야 합니다.

예를 들어 A열에는 부품코드, C열에는 조회한 단가를 표시하고, G열부터 I열까지 별도의 부품표를 둔다고 해보죠. G열은 부품코드, H열은 부품명, I열은 단가입니다. 데이터가 2행부터 6행까지 있다면 C2에 입력한 =VLOOKUP(A2,G2:I6,3,FALSE)는 첫 줄에서는 정상일 수 있습니다. 그러나 C3으로 복사하면 조회 범위가 G3:I7로 밀립니다. 맨 위 부품이 검색 대상에서 빠지고, 아래쪽 빈 행이 새로 포함되는 겁니다.

아래로 복사할 때는 조회표 전체를 고정한다

이 경우 C2의 수식을 =VLOOKUP(A2,$G$2:$I$6,3,FALSE)로 바꾸면 됩니다. 달러 기호가 열 문자와 행 번호 앞에 각각 붙어 있으므로 조회 범위가 G2:I6에 머뭅니다. C3으로 복사한 수식은 =VLOOKUP(A3,$G$2:$I$6,3,FALSE)가 됩니다. 찾을 부품코드는 A3으로 바뀌고, 기준표는 그대로인 상태가 원하는 동작입니다.

수식의 3은 조회 범위에서 세 번째 열인 단가를 가져오라는 뜻입니다. 시트 전체의 I열을 뜻하는 숫자는 아닙니다. 마지막 인수 FALSE는 부품코드가 정확히 일치하는 행을 찾도록 지정합니다. 부품코드처럼 한 글자 차이로 다른 품목이 되는 데이터라면 정확히 일치하도록 설정하는 편이 안전합니다.

조회표가 6행에서 끝나지 않고 계속 늘어난다면 고정 범위도 그에 맞춰 관리해야 합니다. 새 부품을 7행에 추가했는데 수식이 여전히 $G$2:$I$6만 가리키면 새 코드는 찾지 못합니다. 추가가 잦은 재고표라면 조회 데이터를 엑셀 표로 만들어 범위가 확장되도록 관리하는 방법도 편합니다.

행만 고정하는 것과 전체를 고정하는 것은 다르다

‘행고정’이라는 말 때문에 G$2:I$6처럼 행 번호에만 달러 기호를 붙이는 경우가 있습니다. 이 참조는 수식을 아래로 복사할 때 행이 움직이지 않습니다. 하지만 오른쪽으로 복사하면 G열부터 I열까지의 위치가 H열부터 J열까지로 바뀔 수 있습니다. 수식을 아래로만 채울 계획이라면 동작하지만, 가로로도 복사할 재고표라면 $G$2:$I$6처럼 열과 행을 모두 고정하는 쪽이 확실합니다.

반대로 조회값인 A2까지 $A$2로 고정하면 다른 문제가 생깁니다. C열의 수식을 아무리 아래로 복사해도 모든 행이 A2의 부품코드만 찾습니다. 조회표는 고정하고, 각 행에서 찾을 코드는 이동하게 두는 것이 기본입니다. 달러 기호를 붙이는 위치에 따라 결과가 달라지니, ‘수식 전체를 고정한다’고 생각하기보다 각 참조가 복사 후 어디를 가리켜야 하는지 확인하는 편이 좋습니다.

고정했는데도 값이 안 나오면 확인할 곳

조회 범위를 고쳤는데도 #N/A가 나온다면 먼저 찾는 코드가 조회 범위의 첫 번째 열에 있는지 보세요. 앞의 예시에서는 부품코드가 G열에 있어야 합니다. 코드가 H열에 있고 범위를 G열부터 잡으면 VLOOKUP은 H열을 기준으로 검색하지 않습니다. 코드가 같아 보여도 한쪽은 숫자, 다른 쪽은 문자로 저장되어 있거나 앞뒤에 공백이 있으면 일치하지 않을 수도 있습니다.

값은 나오는데 다른 항목의 가격처럼 보인다면 수식 끝이 FALSE인지, 세 번째 인수가 원하는 열 번호인지 확인하는 게 빠릅니다. 또 조회표에 같은 부품코드가 여러 번 있으면 VLOOKUP은 먼저 발견한 값을 가져옵니다. 부품코드를 한 번씩만 등록해야 하는 표라면 중복 데이터도 함께 점검해야 합니다.

재고표에서는 작은 참조 오류 하나가 여러 행의 단가를 바꿔 놓을 수 있습니다. 수식을 채운 뒤 첫 행과 마지막 행에서 조회값과 범위를 각각 확인하는 습관이, 나중에 가격을 다시 대조하는 수고를 꽤 줄여줍니다.