안녕하세요. 행아아빠 입니다. Show 오늘 엑셀 내용으로는 Vlookup 함수를 사용해서 결과값을 가져올 때 찾을 조건에 대한 결과값이 여러 개 있을 때 중복으로 표기 되는 값 없이 표기 할 수 있는 수식 작성 방법입니다. 오늘 내용도 많은 분들께 유용하게 사용할 수 있는 내용이 될 수 있기를 바라겠습니다. ■ Vlookup 함수 조건에 일치 하는 값이 여러개일 때중복된 결과값 없이 순서대로 표기 하는 수식.Vlookup 조건을 만족 하는 결과값을 첫번째값, 두번째값, 세번째값...을 표기 하기 위해서 간단히 작성한 서식 이미지왼쪽 표에서 A종류의 수량을 결과값으로 가져 오기 위해서 Vlookup 함수로 F4셀에 수식을 작성하게 되면 =VLOOKUP($F$3,$B$3:$C$11,2,FALSE) 으로 수식을 작성 할 수 있고, 결과값은 8,000으로 표기 됩니다. 다만 위 수식으로 A종류의 모든 결과값을 순서대로 표기 하지는 못하고 A종류의 두번째 결과값, 세번째, 네번째 결과값도 모두 동일하게 8,000으로 표기 됩니다. 또한 A종류는 3개의 결과값만 존재 하게 되는데 수식을 계속해서 작성하더라도 결과값은 8,000이 나오게 되는 것이죠. Vlookup 조건 A의 결과값을 오른쪽 표에 순서대로 수식을 작성해 표기한 이미지찾을 값의 조건이 B열의 종류가 A라면 결과값은 ① 8,000, ② 3,000, ③ 7,000 이렇게 3개의 값이 순서대로 작성되야 합니다. 그렇다면 오른쪽 표에 찾을값의 첫번째, 두번째, 세번째.... 값을 표기 하기 위해서 수식을 어떻게 작성해야 할까요? 방법은 참조 할 수 있는 보조 열을 사용해서 수식을 작성 할 수 있는데 보조열을 수식으로 작성 하는 수식과 Vlookup 함수 수식을 중복값 없이 순서대로 표기 하는 수식을 작성해 보도록 하겠습니다. Vlookup 함수를 사용해서 한가지 조건을 만족시키는 여러개의 결과값을 순서대로 표기한 수식과 보조열인 A열의 수식보조열을 사용해서 Vlookup 함수로 찾을 조건인 A의 결과값을 순서대로 입력할 수 있습니다. 우선 보조열인 A열에 각각의 종류의 순서를 참조 할 수 있도록 A1, B1...A2, B2...으로 표기 해 주기 위해서 수식을 작성하면 다음과 같이 작성 할 수 있습니다. =$B3&COUNTIFS($B$3:B3,B3) Countifs함수를 응용해서 개수와 문자를 연결 시켜 줄 수 있도록 작성한 수식 입니다. 수식끼리 연결해서 숫자 또는 문자를 연속해서 표기 하기 위해서는 "&" 기호를 연결 고리로 사용해 주시면 됩니다. 보조열에 작성한 수식의 결과값을 참조해서 Vlookup함수로 오른쪽 표에 찾을 값의 조건이 A일때 결과값을 순서대로 표기 하기 위한 수식은 다음과 같이 작성 할 수 있습니다. =VLOOKUP($F$3&$E4,$A$3:$C$11,3,FALSE) 찾을 조건은 F3셀인 A에다가 첮번째 값인 1이 작성되어 있는 E4셀을 연결해 주어 A1값을 찾을 수 있도록 Vlookup함수를 응용해서 수식을 작성해 주었다고 보시면 되겠습니다. 즉, A1의 수량, A2의 수량, A3의 수량의 결과값을 구하는 수식으로 변경 된 것입니다. 그런데, 여기서 표를 보기에 지저분한 수식 오류값이 표기 되는데 이것을 보기 좋게 표기 하기 위해서는 Iferror함수를 사용해 주시면 보기 깔끔하게 결과값을 표기할 수 있겠습니다. Iferror함수를 추가로 사용해서 수식을 작성해 주면 다음과 같이 작성 할 수 있겠습니다. =IFERROR(VLOOKUP($F$3&$E4,$A$3:$C$11,3,FALSE),"") 위와 같이 Iferror함수를 사용해서 수식을 작성해 주게 되면 #N/A의 오류 값으로 표기 되는 것이 공란으로 표기 되어 표를 깔끔하게 볼 수 있습니다. 만약 보조열에 작성한 A열을 깔끔하게 정리 하고 싶다면 숨기기 기능으로 A열을 숨겨주어 깔끔하게 표를 볼 수 있도록 해 주시면 되겠습니다. 많은 분들께서 유용하게 사용할 수 있는 방법이길 바라면서 오늘 하루도 좋은 하루 되시길 바라겠습니다.^^!
영상강의
큰 화면으로 보기 예제파일 다운로드오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
VLOOKUP 여러개 값 불러오기 공식 사용법이번 강의에서는 VLOOKUP 여러개 값 불러오기 공식의 기초 및 응용공식과 동작원리를 단계별로 살펴봅니다.
365 버전 사용자는 이번 강의에서 소개해드린 공식 대신 FILTER 함수를 사용하면 더욱 빠르고 편리하게 작업할 수 있습니다. FITLER 함수에 대한 자세한 설명은 아래 FILTER 함수 실전예제 총정리 영상강의를 참고하세요. VLOOKUP 여러개 값 불러오기 기본 공식 (조건이 1개인 경우) =IFERROR(INDEX($출력범위, SMALL(IF(($찾을값=$찾을범위),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"") 엑셀 VLOOKUP 여러개 값 출력 기본 공식VLOOKUP 여러개 값 불러오기 응용 공식 #1 (각 조건으로 여러 범위를 비교하는 경우) =IFERROR(INDEX($출력범위, SMALL(IF(($찾을값1=$찾을범위1)*($찾을값2=$찾을범위2),MATCH(ROW($찾을범위), ROW($찾을범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"") 엑셀 VLOOKUP 여러개 출력 - 여러 범위를 조건으로 비교하는 경우VLOOKUP 여러개 값 불러오기 응용 공식 #2 (한 범위에서 여러 조건을 찾는 경우) =IFERROR(INDEX($출력범위,SMALL(IF(ISNUMBER(MATCH($조건범위,$조건,0)),MATCH(ROW($출력범위), ROW($출력범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"") 엑셀 VLOOKUP 여러개 출력 - 한 범위에서 여러 조건을 찾는 경우VLOOKUP 여러개 값 불러오기 응용 공식 #3 (여러 범위에서 여러 조건을 찾는 경우) =IFERROR(INDEX($출력범위,SMALL(IF(ISNUMBER(MATCH($조건범위1,$조건1,0)*MATCH($조건범위2,$조건2,0)),MATCH(ROW($출력범위), ROW($출력범위)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)),"") VLOOKUP 함수 사용시 제한사항VLOOKUP 함수는 SUMIF 함수와 함께 실무자가 가장 많이 사용하는 엑셀 10대 필수 함수 중 하나입니다. 가장 자주 사용되는 함수이지만, 실무에서 발생하는 다양한 상황에 적용할 때에는 몇가지 제한사항이 있어서 다른 함수나 공식으로 대체해서 사용해야 합니다.
MATCH+ROW 함수로 순번 만들기VLOOKUP 여러개 값 불러오기 공식의 첫번째 계산은 MATCH+ROW 공식에서 시작됩니다. MATCH+ROW 공식을 사용하면 1부터 증가하는 연속되는 순번을 배열로 반환할 수 있습니다.
IF 함수로 조건을 만족할 경우 순번 출력하기이제 IF 함수를 사용해서, 특정 조건을 만족하는 경우에만 MATCH+ROW 함수로 계산된 순번이 반환되도록 공식을 입력합니다.
ROWS+확장범위로 증가하는 순번 만들기ROWS 함수와 확장범위를 활용하면 아래로 자동채우기를 할 때마다 1씩 증가하는 연속된 순번을 만들 수 있습니다. ROWS+확장범위 공식은 다양한 자동화 배열수식에 활용할 수 있으므로 엑셀 고급단계로 넘어가기 위한 실무자라면 반드시 숙지하시길 권장드립니다.
SMALL 함수로 값을 차례대로 불러오기이제 SMALL 함수를 활용하여, 우리가 방금 전 IF 함수로 반환한 순번 범위의 값을 작은 값 부터 하나씩 차례대로 출력합니다.
INDEX/MATCH 공식 완성하기이제 INDEX 함수를 사용하여 VLOOKUP 여러개 값 출력하기 공식을 완성합니다.
|