엑셀 VLOOKUP 두 번째·n번째 값 찾기 공식
지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀
반복된 조회 키의 두 번째·n번째 대응값을 찾는 공식입니다.
인수 설명
012021 이후 버전 공식
=INDEX ( FILTER ( 출력범위, 찾을범위=찾을값 ), N번째 )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
출력범위필수찾은 항목에 대응해 반환할 값이 입력된 범위입니다. 찾을범위와 행 수가 같아야 합니다.
찾을범위필수찾을값과 비교할 조회 키가 입력된 범위입니다. 출력범위와 행 수가 같아야 합니다.
찾을값필수반복된 조회 키에서 찾을 값입니다.
N번째필수일치한 값 중 몇 번째 대응값을 반환할지 지정하는 양의 정수입니다.
A
B
C
D
E
F
G
1
상품
주문수량
찾을상품
순번
결과
2
사과
120
사과
3
130
3
배
90
4
사과
150
5
귤
80
6
사과
130
7
배
110
8
사과
170
9
02모든 버전 호환 배열수식
{=INDEX ( 출력범위, SMALL ( IF ( 찾을값=찾을범위, ROW ( 찾을범위 )-ROW ( 시작셀 )+1 ), N번째 ) )}인수 설명 자세히 보기
인수구분설명
출력범위필수찾은 항목에 대응해 반환할 값이 입력된 범위입니다. 찾을범위와 행 수가 같아야 합니다.
찾을값필수반복된 조회 키에서 찾을 값입니다.
찾을범위필수찾을값과 비교할 조회 키가 입력된 범위입니다. 출력범위와 행 수가 같아야 합니다.
시작셀필수찾을범위의 첫 번째 셀입니다. 행번호를 1부터 시작하도록 보정하므로 찾을범위의 시작셀을 지정합니다.
N번째필수일치한 값 중 몇 번째 대응값을 반환할지 지정하는 양의 정수입니다.
A
B
C
D
E
F
G
1
상품
주문수량
찾을상품
순번
결과
2
사과
120
사과
3
130
3
배
90
4
사과
150
5
귤
80
6
사과
130
7
배
110
8
사과
170
9
동작 원리
012021 이후 버전 공식
FILTER 함수가 일치하는 대응값만 추리고 INDEX 함수가 그중 n번째 값을 반환합니다.
2021 이후 버전 공식의 예제 시트에서 F2 셀이 130을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
FILTER 함수가 일치하는 대응값을 추립니다
FILTER 함수는 A2:A8에서 D2의 사과와 같은 행만 판정해 B2:B8의 대응값 네 개를 세로 배열로 반환합니다.
=FILTER ( B2:B8, A2:A8=D2 )
= {120;150;130;170}
사과와 일치한 네 행의 대응값입니다. 2
INDEX 함수가 n번째 값을 선택합니다
INDEX 함수는 FILTER 함수가 반환한 배열에서 E2가 지정한 세 번째 값을 선택해 130을 반환합니다.
=INDEX ( FILTER ( B2:B8, A2:A8=D2 ), E2 )
= 130
세 번째 대응값입니다. 02모든 버전 호환 배열수식
ROW 함수와 IF 함수가 일치 행의 순번을 만들고, SMALL 함수와 INDEX 함수가 n번째 대응값을 반환합니다.
모든 버전 호환 배열수식의 예제 시트에서 F2 셀이 130을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
ROW 함수가 상대 순번을 만듭니다
ROW 함수로 A2:A8의 실제 행번호를 구하고 A2의 행번호를 빼 1부터 7까지의 상대 순번을 만듭니다.
=ROW ( A2:A8 )-ROW ( A2 )+1
= {1;2;3;4;5;6;7}
A2:A8의 각 행에 대응하는 상대 순번입니다. 2
IF 함수가 일치 행의 순번만 남깁니다
IF 함수는 D2의 사과와 같은 행에는 상대 순번을 남기고, 다른 행에는 FALSE를 반환합니다.
=IF ( D2=A2:A8, ROW ( A2:A8 )-ROW ( A2 )+1 )
= {1;FALSE;3;FALSE;5;FALSE;7}
사과가 있는 행의 상대 순번입니다. 3
SMALL 함수가 n번째 순번을 찾습니다
SMALL 함수는 FALSE를 제외한 숫자 순번에서 E2가 지정한 세 번째로 작은 값 5를 반환합니다.
=SMALL ( IF ( D2=A2:A8, ROW ( A2:A8 )-ROW ( A2 )+1 ), E2 )
= 5
세 번째 일치 행의 상대 순번입니다. 4
INDEX 함수가 대응값을 반환합니다
INDEX 함수는 B2:B8의 다섯 번째 값인 130을 반환합니다.
=INDEX ( B2:B8, SMALL ( IF ( D2=A2:A8, ROW ( A2:A8 )-ROW ( A2 )+1 ), E2 ) )
= 130
세 번째 대응값입니다. 
추가 질문이 있는데,
찾을범위의 이름이 <정현수 임규리 김예진>, <정현수 김예진> 이런 식으로 섞여 있어도
찾을값으로 <정현수>를 넣으면 찾을범위 이름 내에서 정현수가 포함된 모든 결과값이 나오도록 하는 방법은 없을까요?
와일드카드 "*"를 써서, 아래 처럼 넣으면 에러가 뜨더라구요 ㅠ
{ =INDEX($출력범위,SMALL(IF("*"&$찾을값&"*"=$찾을범위,ROW($찾을범위)-ROW($시작셀)+1),N번째)) }
ISNUMBER/SEARCH 함수를 활용해보세요 ^^
{ =INDEX($출력범위,SMALL(IF(ISNUMBER(SEARCH(찾을값,찾을범위)),ROW($찾을범위)-ROW($시작셀)+1),N번째)) }
https://www.oppadu.com/if-%ED%95%A8%EC%88%98-%ED%8A%B9%EC%A0%95-%EB%AC%B8%EC%9E%90-%ED%8F%AC%ED%95%A8/
혹시 한 시트내에서가 아니라 다른 엑셀에 있는 자료를 가져올때는 안되는걸까요~??
혹시 방법이 있을까요?
다른 엑셀파일에서 불러오는 것도 가능합니다.^^
다만 다른 엑셀파일에서 불러올 경우에는 해당 엑셀파일이 반드시 실행된 상태여야 합니다.
이게, 가지는 의미는 알겠는데, 구조를 이해하기 어려워 문의드립니다.
=ROW(D8:D12)-ROW(D8)+1
={8,9,10,11,12}-8+1
={1,2,3,4,5}
-> D8~D12 구간에서 ROW 함수를 쓰면 8이 반환이 될텐데, 거기에 D8+1 ROW함수를 해서 더한 값을 빼면
결국 8-8+1의 행의 값을 가져오게 될텐데,
저걸 단순하게 생각해보니, 그냥, 그냥 범위 내에서 값을 반환하면 되는 것 아닐까? 왜 전체 범위 - (전체 범위)+1을 하지? 라는 의문이 드는데,
아무래도 제가, 이해력이 부족한가 봅니다.
설명 좀 부탁드립니다 ㅠㅠ
+1 을 해주지 않을 경우, 첫번째 값이 1-1 = 0 이 반환되어 오류가 발생하기 때문에, 1-1+1 = 1 로 첫째 행을 받아오도록 1을 더해줍니다.
답변이 도움이 되셨길 바랍니다. 감사합니다!