{엑셀} VLOOKUP 함수, 오류 해결 방법

 

안녕하세요? 제임스(15james)입니다. 오늘은 어제 올렸던 매출대장에서 사용할 VLOOKUP 함수를 배우도록 하겠습니다. 엑셀을 기초부터 시작해서 여기까지 왔는데, 갑자기 VLOOKUP 함수를 배우니 좀 어렵다고 생각하실 수 있지만, 전 실전 능력이 중요하다고 생각합니다.

 

어차피 실전에 다 겪으셔야 할 일이니 매출 대장에서 발생되는 모든 사항을 설명드릴까 합니다. 조금 어려우시더라도 몇 번 보시면서 따라오시면 감사하겠습니다.

---

금액 산출

---

아래 화면을 보면 날자, 지역, 상호 입력하고 다음 품명을 입력하고 수량을 입력하면 자동으로 단가가 나오고 금액이 만들어지면 되겠죠?

 

금액은 =수량 * 단가 이니 예를 들어 G2에는 =E2 * F2 이렇게 입력하면 되겠죠. 그리고 위에서 아래로 마우스 입력 잡업을 이용해 아래로 쭉 복사하면 됩니다.

---

단가 산출

---

문제는 단가를 찾는 문제입니다. 품목에 따라 단가가 다 다른데 이걸 어떻게 한 시트 내에서 해결하겠어요. 그래서 단가 시트를 만들고 단가 시트에 아래 다음 화면과 같이 품명, 단가 테이블을 만들어 둡니다. 이 단가 테이블은 나중에 이름을 지정해 사용하면 더 편리합니다. 이것도 다 해 볼 예정입니다.

 

단가 테이블 입력을 해 드렸으니 단가 시트로 가서 확인해 보세요.

 

F2의 단가를 찾는 방법을 말로 설명하면

 

1. D2의 품명을 확인하고

2. 단가 테이블에서 D2 품명을 찾고

3. 해당되는 단가를 찾아

4. F2에 표시하면 되겠죠?

VLOOKUP 함수는 이것을 해결하는 함수입니다. VLOOKUP 함수 첫 번째 인수가 어떤 값을 찾을 것인지 그걸 입력합니다. F2에서는 D2의 품명이 됩니다.

 

다음 어디서 찾을 것인가인데 아래 품명 단가 테이블에서 찾는 것이니, A2에서부터 B6까지의 범위가 되겠네요. 그다음 어떤 값을 리턴할 것인가? 하는 문제인데, 두번째 줄에 있는 값이 곧 단가입니다. 그러니 2가 되겠지요.

 

VLOOKUP (찾고자 하는 대상 셀, 테이블(여기선 품명 단가 테이블), 리턴할 셀의 열 번호) 이렇게 하면 됩니다. 물론 그 뒤에 비슷한 것을 리턴할 거냐 꼭 맞는 것을 리턴할 거냐가 있는데, 일단 이건 다음에 해결하기로 합니다.

다시 F2 값을 처리하는 과정을 보면 D2의 값을 가져오고("볼펜"입니다), 테이블(단가 시트 중에서 A2에서 D6까지 이므로 단가!A2:D6 이렇게 입력하면 됩니다) 을 입력하고 몇 번째 값을 반환(리턴, 구할 것인지?)하는냐 2번째 값을 리턴(값은 4,000원)합니다. 테이블 내에서 볼펜을 찾고 거기서 2번째 열의 값이니 4,000이 됩니다.

 

이해가 되셨나요? 강의가 대화형이 아니고 일방적으로 제가 말을 하는 것이니 이해가 되셨는지? 안되셨는지? 그게 가장 궁금하네요.

 

F2셀에 입력된 식은 "=VLOOKUP(D2, 단가!A2:B6, 2)" 이렇게 됩니다. 하지만 이렇게 입력하고 마우스로 자동 복사하는 방법을 상용하면 A2는 A3로 B6는 B7으로 이렇게 하나씩 중가하여 테이블이 한칸씩 아래로 이동하기 때문에 변동이 안되도록 절대값을 빠꾸어 입력합니다.

 

"=VLOOKUP(D2, 단가!$A$2:$B$6, 2)" 이렇게요. 이렇게 하고 마우스로 아래로 긁어내리며 복사해도 $A$2:$B$6 는 변하지 않기 때문에 테이블을 고정하여 작업할 수 있게 됩니다.

 

이제 VLOOKUP 함수 설명은 마쳤습니다. 앞으로 이 함수는 아주 자주 사용하는 것이니 꼭 기억하셔야 합니다. 그러나 지금 한방에 이해하실 필요는 없으니 부담은 갖지 마세요.

 

다음 실전 연습할 때 또 나올테니까요.

---

단가에 #N/A처럼 에러가 나지 않게 만들기

---

자 그럼 단가에 에러가 나기 않게 만들어야 합니다. 화면에 에러가 있으면 보기 싫잖아요? 에러가 나지 않기 위한 입력 조건들은 뭐가 있을까요?

 

1. D2에 값이 있어야 하고

2. D2에 문자가 들어와야 하고

3. E2에도 값이 있어야 하고

4. E2엔 숫자가 들어와야 하고

5. D2에 입력한 값이 단가 테이블에서 찾았을 때 있어야 한다

 

이런 조건이 되겠네요. 이런 조건이 되는지 확인하는 식을 만들어 넣으면 되겠네요.

 

---

문자가 입력되지 않았는지 확인하기

---

문자가 입력되지 않았는지 확인하는 방법은 두가지가 있습니다.

 

1. ISBLANK() 함수를 사용하여 입력값이 없는 것을 확인하는 방법과

2. IF()로 입력값이 "" 즉 아무것도 없는지를 확인하는 방법

 

이 있습니다. 아래 화면은 ISBLANK를 사용한 화면입니다.

 

ISBLANK의 인수(참조하는 값)는 해당 셀이므로 F5의 경우엔 =ISBLANK(D5)이렇게 입력하면 됩니다. D5 셀에 값을 입력하지 않았나? 를 확인합니다.

아래 화면은 IF() 함수를 사용하는 벙법입니다. IF() 함수에는 조건과 두가지 리턴값이 있습니다.

1. 첫번째는 조건입니다. F6의 경우 D6의 값이 ""이면, 즉 아무 것도 입력이 되었으면

2. 두번째는 TRUE일 때 즉 참일 때(맞을 때) 어떤 값을 리턴 할 것인지

3. 세번째는 FALSE 즉 거짓 일 때 리턴하는 값을 적으면 됩니다.

 

=IF(D6="", TRUE, FLASE) 이렇게 입력하면 됩니다. 설명드리면 D6의 값이 ""이면 즉 비어있으면, TRUE를 화면에 출력하고 아니면 FLASE를 출력하라 는 뜻입니다.

어떤 것을 쓰는 것이 좋을지는 여러분 취향대로 쓰면 되는데, 단 ISBLANK()를 사용하게 되면 다시 IF() 함수를 또 사용해야 하는 불편이 있지요.

 

일단 문법 길이를 줄이기 위해 IF()를 사용하여 고쳐보겠습니다. 고친 수식을 아래와 같습니다.

 

수정된 수식

=IF(D2 <>"", VLOOKUP(D2, 단가!$A$2:$B$6, 2), "입력오류")

 

설명

D2 (단가) 셀의 값이

<>"" (BLANK가 아니면, 빈칸이 아니면)이면

VLOOKUP(D2, 단가!$A$2:$B$6, 2) 를 실행하고

아니면

"입력오류"를 화면에 출력하라

 

입니다.

이제 다 끝났나요? 아니죠 E2의 값도 확인해야 합니다. 이번에도 똑 같은 방법으로 식을 만들어 넣었습니다.

 

수정된 수식

=IF(E2<>"", IF(D2 <>"", VLOOKUP(D2, 단가!$A$2:$B$6, 2), "입력오류"), "입력오류")

 

설명

실제 식이 길어졌지만, 줄이면 이렇게 =IF(E2<>"", D2 빈칸 확인, "입력오류") 됩니다. 조건문을 만드는 등의 로직을 만들 때에는 이렇게 하나씩 식을 추가하는 것이 좋습니다.

E셀에 숫자가 입력되었는지 확인

출처 입력

이제 남은 일은 E셀에 숫자가 입력되었는지 확인하는 작업만 남았네요. 만일 여기에 숫자가 입력되었을 땐 이렇게 보이겠죠. 수량에 "백"이렇게 입력했습니다. 그랬더니 단가는 그대로 4000인데, 금액이 #VALUE! 이렇게 오류가 났네요.

 

이건 G2 가 E2의 문자와 F2의 숫자를 곱할 수가 없어서 난 오류입니다.

---

입력값이 숫자인지를 확인하는 함수 ISNUMBER()

---

이제 E에도 뭔가 입력이 되어야 하고, 입력된 것이 숫자여야 합니다. 이것을 식으로 만들어 넣으면 되겠죠.

 

수식

=IF(ISNUMBER(E2), E2*F2, "입력오류")

 

설명

E2의 값이 숫자이면 E2*F2 를 하고 아니면 "입력오류" 를 화면에 출력하라 는 뜻입니다.

이제 마지막 작업이 남았습니다. 품명이 단가 테이블에 없는 것을 입력했을 땐 어떻게 되나요? 확인해 봐야겠죠?

 

한번은 단가 테이블에 없는 "지구본"이라 입력했더니 단가가 3,500원이 나왔고, 그 아래 똑같이 단가 테이블에 없는 "노트"를 입력했더니 단가가 #N/A 라고 나왔네요.

 

사실 두개다 틀린 값입니다. 아래 처럼 오류가 나면 좋겠지만, 위처럼 오류가 안나고 계산이 된다면 이건 더 큰 문제입니다. 맞는지 알고 처리하다가 낭패를 보겠죠.

 

자 그럼 이제 VLOOKUP을 다시 한번 보겠습니다.

---

다시 보는 VLOOKUP 함수

---

VLOOKUP 함수에는 아까 설명드리지 않았던 인수 하나가 더 있습니다. 끝에 TRUE 또는 FLASE를 추가하면 해결 됩니다. 끝에 TRUE를 넣는 것은 대충 비슷한 값을 찾아서 리턴한다는 뜻이고, FLASE는 반드시 같은 값을 찾아서 리턴하라는 뜻입니다.

 

예제 1: 기본

=VLOOKUP(J1,A1:C5,3,FALSE)

이 값은 J1 셀의 값을 참조하여 범위 A1:C5의 테이블에서 3번째의 셀의 값을 가져오되, 못 찾을 경우 에러를 발생시켜라 입니다.

 

자 그럼 아까 작업했던 함수 뒤에 FALSE를 추가해 보겠습니다.

 

수정된 수식

=IF(E2<>"", IF(D2 <>"", VLOOKUP(D2, 단가!$A$2:$B$6, 2, FALSE), "입력오류"), "입력오류")

 

설명

반드시 일치해야만 결과를 리턴하도록 수정했습니다. 결과는 이래 화면과 같습니다. 지구본으로 검색했을 때 없으니 #N/A라고 에러가 났음을 표시합니다.

그냥 두어도 문제는 없습니다. 그러나 #N/A와 같이 오류 메시지가 화면에 보이는 것이 싫다면 다음과 같이 수정하면 됩니다. 이제 마지막 오류 수정 작업입니다.

 

수정된 수식

=IFERROR(IF(E2<>"", IF(D2 <>"", VLOOKUP(D2, 단가!$A$2:$B$6, 2, FALSE), "입력오류"), "입력오류"), "품명없음")

 

설명

IFERROR() 함수는 에러가 나면, 다음 인수를 출력하라 입니다. 따라서 해당 셀의 결과가 에러가 나면 뒤에 "품명없음" 을 화면에 출력하는 식이 됩니다.

이제 모든 것이 정리되었네요. 또 다른 에러가 숨어있을 지도 모릅니다. 혹시 발견되시면 수정해 보시고 잘 안되면 저를 찾아 주세요.

 

오류가 나지 않는 엑셀 작업은 할 일이 좀 있지요? 그렇지만 사용자에게 좋은 양식을 제공하려면 이런 작업이 필요합니다. 물론 다른 방법으로 입력을 제한하는 방법도 있습니다. 그건 나중에 설명드리겠습니다.

 

지금까지 제임스였습니다. 수고하셨습니다.