안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
목차
엑셀(excel) 작업을 하면서 vlookup함수와 hlookup함수를 결합한 형태로 작성하여 행과 열을 모두 참조 하여 일치 하는 데이터 값을 결과값으로 계산하여 가져오고 싶을때가 있습니다.
이럴때 사용할 수 있는 함수는 index와 match함수를 인수 함수로 사용하여 수식을 작성할 수 있고, 엑셀(excel) 최신 버전에서는 xlookup함수를 이용해서 행과 열을 참조 하여 일치 하는 값을 가져올 수 있습니다.
그러나 xlookup함수는 엑셀(excel) 최신 버전일지라도 2016, 2019버전에서는 사용 불가 하고 상위 버전에서만 수식 작성이 가능하고 2016버전, 2019버전에서는 상위 버전에서 작성된 xlookup함수를 직접 사용이 불가 하다고 합니다.
따라서 최신 버전의 엑셀을 사용하는분이 아니라면 index함수와 match함수를 인수 함수로 사용하여 행과 열을 모두 참조하여 일치 하는 결과값을 반환하여 표기 할 수 있습니다.
이렇게 행과 열을 참조 하거나 가로 세로를 참조, 또는 vlookup함수와 hlookup함수를 동시에 계산 할 수 있는 수식을 작성하고자 할때 index, match함수를 사용해서 수식을 작성 할 수 있는것이죠.
index match함수의 수식을 작성할 수 있는 샘플 예제 서식을 함께 확인해 보기로 하죠.
1. vlookup + hlookup, 행과 열 참조, 가로 세로 참조 하는 데이터 수식 작성.
이미지에서 보는것처럼 가로 방향(열 방향)으로는 1, 2, 3의 값이고 세로 방향(행 방향)으로는 A, B, C의 값으로 A1의 값은 123과 같이 A1, A2,A3, B1, B2, B3...의 데이터값을 표 형태로 입력한것입니다.
샘플 예제 서식의 입력된 가로 세로의 값을 품명과 색상의 재고 수량, 부품과 부품 번호의 가격등을 정리한것과 같은 데이터 입력값으로 사용자의 필요 서식에 따라 변경할 수 있겠죠.
이렇게 A1의 결과값을 한번에 가져 오기 위해서는 행과 열, 가로 세로를 모두 참조 해야 하기 때문에 vlookup함수로 가로 방향의 데이터 결과값과 hlookup함수로 세로 방향의 데이터 결과값의 일치 하는 셀을 최종 결과값으로 가져오고 싶지만 vlookup함수와 hlookup함수는 서로를 포함하는 인수 함수로 사용할수가 없습니다.
따라서 index, match함수 또는 xlookup함수로 수식을 작성해서 결과값을 가져와야 하는데 엑셀(excel) 버전이 2021버전 이상이 아니라면 xlookup함수는 직접 사용할 수 있는 함수가 아니라는것을 앞서도 말씀 드렸기 때문에 2021버전 이하의 엑셀(excel) 버전 사용자라면 index, match함수를 사용해서 수식을 작성해야 합니다.
가로 세로 형태로 입력된 표 형태의 데이터 값을 조건에 따라 표 데이터 안의 값을 결과값으로 가져오는 수식을 작성 하기 위해서 F2셀에는 조건을 입력할 셀, G2셀에는 index, match함수로 수식을 작성하여 결과값을 표기할 셀을 샘플 예제 서식에 추가 했습니다.
조건이 C2라면 A열에서는 C의값, 1행에서는 2의값의 표안에 데이터 값을 결과값으로 258이라는 결과값을 가져와야겠죠.
G2셀에 C2의 값을 가져오기 위한 수식을 작성해 보면 다음과 같이 작성 할 수 있습니다.
| =INDEX($B$2:$D$4,MATCH("c",$A$2:$A$4,0),MATCH(2,$B$1:$D$1,0)) |
index의 참조 영역은 결과값을 가져올 데이터 값이 입력된 범위이고, match함수의 인수는 찾을 조건, 조건의 참조 영역, (1=유사값중 큰값, 0=정확히 일치 하는값, -1=유사값중 작은값)을 결과값으로 반환하는 것입니다.
이때 match함수의 순서는 세로 방향의 조건 또는 참조 영역이 먼저 작성되어야 하고, 그 다음에 가로 방향의 조건 또는 참조 영역을 작성해야 오류값을 반환하지 않고 원하는 결과값을 가져오게 됩니다.
따라서 b2:d4 범위 안에서 A열에서는 C값과 정확히 일치하는값, 1행에서는 숫자2가 정확히 일치 하는 값을 찾으면 258의 결과값을 가져오게 되는것입니다.
여기서 match함수의 찾을 조건을 "C"와 숫자2를 직접 입력해 주었는데 세로 방향에서 찾을 조건과 가로 방향에서 찾을 조건을 직접 입력해주는 셀이 있다면 각 셀 주소를 입력해주어 수식을 작성해 줄 수 있기 때문에 찾고자 하는 결과값을 좀 더 편하게 가져올 수 있게 되겠습니다.
또한 엑셀(excel) 최상위 버전에서는 xlookup함수로 index, match함수를 대체해서 사용할 수 있다고 했는데 만약 xlookup함수를 사용할 수 있는 엑셀(excel) 버전이라면 다음과 같이 xlookup함수 수식을 작성 할 수 있습니다.
| =XLOOKUP("C",A1:A4,C1:C4) |
하지만 xlookup함수의 단점은 가로 세로의 참조 영역중 데이터 값이 입력된 범위를 찾을 조건의 값이 입력된 범위로 변경해 줘야 하는 단점이 존재하기도 하지만, 수식을 간결하게 작성할 수 있다는점이 쉽게 사용할 수 있는 함수로 볼 수 있겠습니다.
엑셀에 입력된 값중 부품 종류의 가격, 특정 제품의 특정 색상의 재고등과 같은 데이터 값을 찾아야 할일이 있다면 index match함수로 쉽고 편하게 사용해 보시기를 바라겠습니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"vlookup + hlookup, 행과 열 참조, 가로 세로 참조 하는 데이터 수식 작성 방법" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
※ "vlookup + hlookup, 행과 열 참조, 가로 세로 참조 하는 데이터 수식 작성 방법" 샘플 예제 서식 다운로드.
'엑셀(Excel)' 카테고리의 다른 글
| 엑셀(Excel) 연차 남은 개수 사용 개수 발생 개수 수식을 작성해 보자. (0) | 2022.11.16 |
|---|---|
| 엑셀(Excel) 구간 값 사이 값 수식을 작성해 보자. (0) | 2022.11.16 |
| 엑셀(Excel) countifs함수 수식 결과값이 공란(공백) 시간이 입력된 셀의 공란(공백)을 제외하고 개수 세는 수식을 작성해 보자. (0) | 2022.11.15 |
| 엑셀(Excel) 비 근로 시간 계산 점심, 저녁 심야 시간 규칙적으로 발생하는 시간 계산 수식 작성을 해보자. (0) | 2022.11.14 |
| 엑셀(Excel) 4대보험 계산 국민연금, 건강보험, 고용보험, 산재보험 계산 수식을 작성해 보자. (0) | 2022.11.13 |
댓글