(Google SpreadSheet) VLOOKUP function

문서 작성 시, 항목별로 Unique한 ID를 관리해야할 때가 있는데, 순서대로 1,2,3,4, ... 형태로 진행할 경우 추후 중간에 항목이 추가될 때 ID 관리에 어려움이 있을 수 있다.

그렇다고 늘 마지막에 내용을 추가하는 경우, 카테고리 구성에 어려움이 있을 수 있고..

특히 필자의 경우 테스트 케이스를 구글 스프레드시트로 관리하고 있는데, 그런 경우 이런 문제에 봉착하게 되는 경우가 종종 있다. 

 

언젠가 VLOOKUP 함수를 사용하여 ID 관리를 하는 문서를 본 적이 있어서 기억과 구글링과 삽질을 통해 아래와 같이 ID를 자동으로 채워주는 함수를 완성하였다. 

** 하지만 실제 사용을 하지는 못하게 되었다. 협업자 중에 "대분류" 항목이 동일한 경우 Cell 합치기를 적극 도입하길 원하는 분이 있는데, Cell 합치기를 할 경우, 가장 첫번째 행에만 텍스트 데이터가 인식되어 2번째 행부터는 ID를 참조할수 없기 때문이다. 이 문제도 해결 방법이 있을 것 같은데 나중에 좀 더 찾아보기로 하자.

---

아래와 같이 "대분류" "소분류" 그리고 그 소분류에 따라 여러개(No.)의 항목이 존재하는 경우,

ID열을 "대분류ID"-"소분류ID"-"No.(2자리로)" 이렇게 구성하고자 한다.

 

함수 : 

=VLOOKUP(B4,INDEX!$A$2:$B$10,2,0)&"-"&VLOOKUP(C4,INDEX!$D$2:$E$10,2,0)&"-"&TEXT(D4,"00")

위 함수를 단계별로 하나씩 뜯어 보자.

 

&"-"&

• ID와 ID 사이에 대쉬(-)로 연결하는 부분은 &"-"& 를 통해 좌우 텍스트를 "-"로 연결해 주었다.

VLOOKUP(B4,INDEX!$A$2:$B$10,2,0)

• 대분류와 소분류에 해당하는 부분은 구조는 동일하다.
• LIST를 나열한 메인 sheet 외에 "INDEX"라는 이름의 sheet(아래 그림 참고)를 활용하고 있다.
• VLOOKUP 함수 구조는 아래와 같다.
  • VLOOKUP(검색할_키, 범위, 색인, [정렬됨])
  • 예시 : VLOOKUP(10003, A2:B26, 2, FALSE)
  • 열 방향 검색입니다. 범위의 첫 번째 열에서 키를 검색한 다음 키가 있는 행에서 지정된 셀의 값을 반환합니다.
• 필자의 샘플에서 B4에는 "Account"라는 텍스트가 있고, 그 값이 참조  범위인 "INDEX"시트의 A2:B10 범위에 일치하는 키가 있을 경우 범위 중 2번째 열(B열)에 있는 값을 반환하게 되어 있어 Account라는 텍스트가 A2와 일치하기 때문에 B2에 적힌 ACC 값이 반환되는 형태이다.
• 이 때, A2:B10 범위를 $A$2:$B$10 으로 표시한 이유는, 해당 함수 값을 다른 행에 복사할 때 범위가 행마다 자동 계산되어 밀리지 않고 어느 행에서든 A2:B10 이라는 절대적인 범위를 참조할 수 있도록 하기 위해서이다. 

TEXT(D4,"00")

• No. 에 기재된 숫자를 그대로 사용하려면 D4 형태로 갖다 붙여도 되지만, TEXT 함수를 사용하여 자릿수를 2자리로 고정하여 1자리 숫자의 앞에 0을 붙이도록 하였다.
• 이는 ID가 텍스트 형태일 때, 자릿수를 통일시켜 정렬(2나 3이 10이나 11보다 먼저 정렬)에 문제없기 위해 활용할 수 있다.