일상
[Excel] VLOOKUP 사용해보기
jih0ssang
2025. 2. 5. 16:21
운영팀으로 옮긴 이후, 인벤토리 관리 등 엑셀 함수 사용할 일이 생겨 적어둔다...
VLOOKUP 함수 적용 방식
Sheet 취약점 리스트, 서버 리스트는 서로 각기 다른 Excel 파일이다.
Sheet A에 각 IP를 가진 서버의 서비스명과 hostname을 Sheet B를 참고하여 추가하고자 한다.
[Sheet 취약점 리스트]
| 진단 현황 | IP |
| 취약점 대상 | 10.20.30.40 |
| 취약점 비대상 | 20.30.40.50 |
| 취약점 대상 | 30.40.50.60 |
[Sheet 서버 리스트]
| hostname | Private DNS | Private IP | OS | 서비스명 |
| server-a | ip-10-20-30-40-internal | 10.20.30.40 | Amazon Linux2 | 서비스 A |
| server-e | ip-60-70-80-90-internal | 60.70.80.90 | RHEL 8 | 서비스 D |
| server-b | ip-20-30-40-50-internal | 20.30.40.50 | Cent OS | 서비스 B |
| server-c | ip-30-40-50-60-internal | 30.40.50.60 | RHEL 8 | 서비스 C |
필요한 데이터만 뽑아 정렬
[Sheet 취약점 리스트]
| A | B | C | D | |
| 1 | 진단 현황 | IP | 서비스 | hostname |
| 2 | 취약점 대상 | 10.20.30.40 | =VLOOKUP($C2,서버 리스트!$A1:C5,3,False) | =VLOOKUP($C2,서버 리스트!$A1:C5,2,False) |
| 3 | 취약점 비대상 | 20.30.40.50 | ||
| 4 | 취약점 대상 | 30.40.50.60 |
[Sheet 서버 리스트 필요한 데이터만 정렬]
| A | B | C | |
| 1 | Private IP | hostname | 서비스명 |
| 2 | 10.20.30.40 | server-a | 서비스 A |
| 3 | 60.70.80.90 | server-e | 서비스 D |
| 4 | 20.30.40.50 | server-b | 서비스 B |
| 5 | 30.40.50.60 | server-c | 서비스 C |
VLOOKUP 형식
=VLOOKUP(취약점리스트의 첫 IP 행, 서버리스트의 IP 포함 전 범위, 알고자하는 열 넘버, False)
예시
=VLOOKUP(H4, Sheet2!A1:C2412, 2, FALSE)
- H4 데이터와 동일한 데이터를 Sheet2!A1:C2412 에서 찾아라
- 찾은 데이터 중 오른쪽 Sheet 기준으로 1, 2, 3 중 2번째 데이터 가져와라
- FALSE: 데이터 동일 / TRUE: 데이터 유사
- VLOOKUP 함수 적용된 행 복사 > 전체 행 선택 > 붙여넣기
꿀팁
Command + Shift + ↓ : 전체 선택 (맨아래에서부터 올라오는 걸 추천)
행 내용이 모두 비워져있는 경우, 맨 아래 행에 . 으로 채워서 행의 끝 지정
피벗 테이블(Pivot Table) 생성
서버가 몇 대이고 어떤 서비스인지 식별하고자 한다.
[Sheet 서버 리스트]
| A | B | C | |
| 1 | Private IP | hostname | 서비스명 |
| 2 | 10.20.30.40 | server-a | 서비스 A |
| 3 | 60.70.80.90 | server-e | 서비스 D |
| 4 | 20.30.40.50 | server-b | 서비스 B |
| 5 | 30.40.50.60 | server-c | 서비스 C |
- Sheet 서버 리스트의 C열을 전체 클릭한다.
- C열을 전체 클릭한 다음, 삽입 탭 클릭
- 표 또는 범위 선택 확인 후, 확인 버튼 클릭
- 피벗 테이블 필드에 뜨는 서비스를 아래 영역 '행' 필드와 '값' 필드에 끌어 놓기
피벗 테이블 완성!