엑셀 중복값 가져오기 VLOOKUP보완 [초보편#02]

{=INDEX(D2:D5,SMALL(IF(B2:B5=G2,ROW(B2:B5)-1),COLUMN(A1)))}

G2과 B2:B5 범위에 중복된 조건의 D2:D5 값을 순서대로 가져오는 배열 수식

---

목록

• 중복값 가져오기 수식 활용
• 중복값 가져오기 수식 따라하기
• 마무리

---

VLOOKUP함수를 이용하면 중복된 조건의 값이 있어도 항상 첫 번째 검색되는 값만을 가져옵니다.

중복 값 가져오기 수식을 사용하면 같은 조건의 두 번째, 세 번째 값을 가져올 있습니다.

 

#01. 중복값 가져오기 수식 활용

1. 사업자등록번호의 모든 공급가액 확인하기

	A	B	C	D	E	F	G	H	I
1	날짜	사업자 등록번호	상호명	공급가액		검색조건	입력값	공급 가액1	공급 가액2
2	10/2	4028100000	(주)가나	4,000		사업자 등록번호	4028100000	4,000	6,000
3	10/3	4028100001	(주)다라	5,000		상호명	(주)가나	4,000	6,000
4	10/4	4028100000	(주)가나	6,000
5	10/5	4028100002	(주)마바	7,000

G2에 입력한 사업자등록번호 4028100000와 같은 D2, D4의 공급가액을 가져온다.

G3에 입력한 상호명 (주)가나와 같은 D2, D4의 공급가액을 가져온다

VLOOKUP함수와 다르게 중복된 다음 값을 가져온다.

2. 사업자등록번호와 같은 날짜 가져오기

	A	B	C	D	E	F	G	H	I
1	날짜	사업자 등록번호	상호명	공급가액		검색조건	입력값	날짜1	날짜2
2	10/2	4028100000	(주)가나	4,000		날짜	4028100000	10/2	10/4
3	10/3	4028100001	(주)다라	5,000
4	10/4	4028100000	(주)가나	6,000
5	10/5	4028100002	(주)마바	7,000

G4에 입력한 사업자등록번호 4028100000와 같은 A2, A4의 날짜를 가져온다

VLOOKUP함수는 범위가 정해져 있지만 중복 값 가져오기 수식을 활용하면 범위 지정이 자유롭다

VLOOKUP함수는 사업자등록번호 기준으로 날짜가 D에 위치해야 한다.

#02. 중복값 가져오기 수식 따라하기 

1. 사업자등록번호의 모든 공급가액 가져오기

{=INDEX($E$4:$E$23,SMALL(IF($C$4:$C$23=$H$3,ROW($C$4:$C$23)-3),COLUMN(A4)))} 붙여넣기 하면된다.
{ }배열 수식으로 Ctrl + Shift를 동시에 누른상태에서 Enter를 입력하면 자동으로 { }를 생성한다.

---

①과 ②를 비교해서 ③값을 구한다.

②사업자등록번호와 같은 공급가액을 값1, 값2, 값3, 값4, 값5에 가져온다

사사업자등록번호가 같은 1번, 7번, 13번, 17번, 20번의 값을 자동으로 가져온다.

2. 사업자등록번호와 같은 날짜 가져오기

{=INDEX($B$4:$B$23,SMALL(IF($C$4:$C$23=$H$5,ROW($C$4:$C$23)-3),COLUMN(A4)))} 붙여넣기 하면된다.
{ }배열 수식으로 Ctrl + Shift를 동시에 누른상태에서 Enter를 입력하면 자동으로 { }를 생성한다.

---

C셀의 사업자등록번호와 H5의 사업자등록번호를 비교해서 B셀의 날짜를 순차적으로 가져온다.

중복 값 가져오기 수식은 숫자와 문자 날자 등 다양한 형식이 가능하다.

3. 중복 값 가져오기 수식 이해하기

---

① 가져오고 싶은 값 범위를 지정한다.

날짜를 가져오고 싶다면 $B$4:$B$23로 범위를 정한다.

공급가액을 가져오고 싶다면 $E$4:$E$23로 범위를 정한다.

 

② 비교 범위를 지정한다.

사업자등록번호를 비교하고 싶다면 C열로 지정하고, 상호명을 비교하고 싶다면 D열로 지정한다. 

ROW($C$4:$C$23)-3 수식에서 -3은 행번호를 1로 만들기 위해서 입력한다.

ROW($C$5:$C$23)로 시작한다면 -4를 입력하면 된다. 

 

③ COLUMN함수 열 번호 반환 함수

SMALL 함수의 값을 열이 이동할 때마다 증가시켜 배열수식에서 구한 값을 하나씩 가져오는 방법입니다. 

COLUMN의 값을 절대값으로 표현하면 중복 값을 가져오지 못한다.

#03 마무리

중복 값 가져오기 수식에 적용된 함수는 INDEX, IF, SMALL, ROW, COLUMN함수 입니다.

INDEX 함수는 참조 내에서 행과 열이 만나는 교차지점을 값을 반환합니다.

IF 함수는 논리식의 결과에 따라 다른 값을 반환합니다.

SMALL 함수는 배열에서 K번재로 작은 값을 반환합니다.

ROW, COLUMN 함수는 행과 열을 반환합니다.

엑셀을 업무에 활용하기 위해 많은 함수를 이해하는 것도 중요하지만 하나하나의 함수만을 이해한다해도 업무에 활용하기에는 어려운 부분이 있습니다. 

이해하기보다는 잘 된 수식을 저장해 놓고 적재적소에 사용한다면 업무시간을 단축시킬 수 있습니다.

엑셀을 처음 접하는 직장인분들 실무에 많이 활용하세요 감사합니다.