엑셀 함수와 수식을 작성하여 자동 문서 서식을 작성할때 Vlookup함수와 Hlookup함수와 같이 행 또는 열 방향 범위를 참조하여 결과값으로 반환하고자 하는 경우가 있습니다.
그러나 Vlookup함수와 Hlookup함수는 각각의 함수로만 수식 작성이 가능하고 서로를 인수로 사용하거나 혼합해서 사용해서 행 열을 모두 만족하는 데이터값을 찾을 수는 없다는 한계를 갖고 있습니다.
상위 버전의 Excel에서는 Xlookup함수가 추가 되어 행 열을 모두 참조할 수 있는 함수가 추가 되었지만 Xlookup함수 역시 단일 조건만을 만족하는 값을 결과값으로 가져온다는 한계가 있습니다.
이렇게 행 열 참조범위 안에서 조건을 모두 만족하는 데이터값을 결과값으로 가져오고자 하는 경우 index, match함수로 수식을 작성하여 해결 할 수 있습니다.
index, match함수를 사용하여 적용할 수 있는 문서 서식의 형태는 굉장히 다양한 형태에 적용할 수 있기 때문에 기억해 둔다면 굉장히 유용하게 사용할 수 있을것입니다.
특히 이번 글에서는 가로 세로 조건 값을 모두 만족하는 데이터값을 결과값으로 가져오기 위한 수식으로 여러분들이 엑셀 자동 서식을 완성하는데 도움이 될 수 있으리라 생각합니다.
위 이미지는 이번 글을 설명하기 위해 작성한 간단한 예제 데이터로 A열 사이즈, 1행에는 종류를 입력하고 B2셀 ~ D5셀에는 종류와 사이즈에 해당하는 재고 숫자를 작성해 보았습니다.
A1:D5 범위에서 F2셀 종류, G2셀 사이즈를 입력했을때 종류와 사이즈에 해당하는 재고 숫자값을 H2셀에 결과값으로 가져오고자 합니다.
Vlookup Hlookup함수 수식으로 결과값 가져오기.
조건을 찾아야 하는 범위의 형태가 열을 참조하는지, 행을 참조하는지에 따라 vlookup함수 또는 hlookup함수로 수식을 작성할 수 있습니다.
H2셀의 재고를 결과값으로 가져오기 위해서 vlookup함수와 hlookup함수 수식을 작성하여 확인해 보도록 하겠습니다.
Vlookup함수 수식.
vlookup함수 수식으로 H2셀 재고 값을 가져오기 위해서는 다음과 같은 수식을 작성할 수 있습니다.
=VLOOKUP($G$2,$A$1:$D$5,3,FALSE) |
vlookup함수는 조건을 참조 범위 첫번째 열에서 찾아야 하기 때문에 “850” 사이즈를 찾아야 합니다.
따라서 찾을 조건은 G2셀, 참조범위 A1:D5, 재고값을 가져올 종류는 “B”이므로 가져올 재고값이 참조범위에서 3번째 열에 위치해 있으므로 3으로 수식을 작성해 주면 종류가 “B”이고 사이즈가 “850”인 재고값 “25”를 결과값으로 반환할 수 있습니다.
Hlookup함수 수식.
hlookup함수 수식으로 H2셀 재고 값을 가져오기 위해서 다음과 같은 수식으로 작성할 수 있습니다.
=HLOOKUP(F2,$A$1:$D$5,3,FALSE) |
hlookup함수는 행을 기준으로 조건을 찾아야 하기 때문에 찾을 조건은 “B”가 되고, 찾아야할 사이즈는 “850”이므로 A1:D5 참조 범위 내에서 “850”사이즈가 위치한 행은 3번째 행이 되기 때문에 위와 같은 수식으로 종류가 “B”이고 사이즈가 “850”인 재고값 “25”를 결과값으로 반환하는 수식을 작성할 수 있습니다.
index match 가로 세로 조건 값 반환 수식.
위에서 Vlookup함수와 Hlookup함수로 원하는 결과값을 가져오기 위한 수식을 작성해 봤는데, 두 함수 모두 한가지 조건을 만족시키는 결과값을 반환하고 있고, 조건을 찾을 기준이 가로냐 세로냐에 따라 사용 함수를 다르게 적용해야 합니다.
또한 찾아올 값이 위치한 행, 열의 위치를 일일이 지정해줘야 하므로 찾을 조건이 달라지게 되어 결과값의 행 열의 위치가 변할 경우 결과값을 동적으로 반영하여 반환하지 못한다는 한계가 있습니다.
따라서 가로 방향으로는 종류의 조건을 찾고, 세로 방향으로 사이즈와 동일한 조건을 찾아 결과값을 반환하는 수식을 작성하면 찾을 조건에 따라 수식을 수정해 주지 않고도 원하는 결과값을 가져올수 있습니다.
index match함수 수식으로 결과값을 가져오기 위한 수식을 작성하면 다음과 같습니다.
| =INDEX($B$2:$D$5,MATCH($G$2,$A$2:$A$5,0),MATCH($F$2,$B$1:$D$1,0)) |
| index 함수 수식 구문.
index(결과값을 가져올 참조 범위, 행 번호, 열번호) |
| match함수 수식 구문.
=match(찾을 조건, 조건을 찾을 참조 범위, match type) |
위 수식을 작성으로 작성하게 되면 F2셀 종류와 G2셀 사이즈의 값을 변경하더라도 수식을 수정하지 않고도 원하는 결과값을 가져오게 됩니다.
결과값을 가져올 범위는 B2:D5, 사이즈를 찾을 범위는 A2:A5, 종류를 찾을 범위는 B1:D1로 match함수로 각각의 행 번호, 열번호를 찾도록 인수로 작성하여 찾을 조건 값이 달라지더라도 가로 세로 조건값을 찾아 결과값을 가져오게 됩니다.
글을 마치며…[행아아빠]
글의 서두에서도 작성했지만 Vlookup함수는 조건을 참조하여 결과값을 가져오는 유용한 함수이지만 2개 이상의 조건을 만족하는 결과값을 반환할 수 없기 때문에 당연히 가로 세로 2가지 조건 값을 만족하는 결과값을 가져올 수 없다는 한계점이 있습니다.
대부분의 문서 서식이 가로 세로 형태와 같은 표 형태로 데이터를 작성한다고 했을때 본문에서 설명한 index match 수식을 작성할 수 있다면 그동안 vlookup함수, hlookup함수를 기준에 따라 다르게 사용했을때 보다 훨씬 효율적으로 수식을 작성하고 결과값을 동적으로 유연하게 가져올 수 있는 수식 작성 방법이 될 수 있을것입니다.
