가로, 세로로 일치하는 데이터 찾기

 

1. 문제

index,match.xlsx

0.01MB

 

 

왼쪽에 데이터가 있고,

 

오른쪽에 prc_name과 eq.no가 일치하는 날짜를 찾는 것입니다.

 

2. 여러 가지 해결방안

prc_name과 eq.no에 해당하는 값을 찾은 다음 offset 함수를 이용해 오른쪽으로 다섯 번째 날짜를 찾을 수도 있고,

index 함수를 이용해 날짜 범위를 지정한 후 행과 열 수를 지정해서 원하는 값을 구할 수도 있습니다.

 

3. Offset 함수 이용

가. Offset 함수의 구문

OFFSET(reference, rows, cols, [height], [width])로

reference는 참조 셀

rows는 이동할 행 수로 +면 아래, -면 위로 이동하고, 0은 현재 위치를 가리킵니다.

cols는 이동할 열 수로 +면 오른쪽, -면 왼쪽으로 이동하고, 0은 현재 위치를 가리킵니다.

 

height는 높이, 다시 말해 세로 개수가 되고

width는 너비, 다시 말해 가로 개수가 됩니다.

 

나. 행과 열 수 구하기

(1) 행수 구하기

행을 구하는 것은 1 열이기 때문에 간단합니다.

=MATCH(M2,$A$2:$A$400,0)는

prc_name에 일치하는 값이 A2셀에서 A400셀 중 몇 번째에 있는지를 구하는 것입니다.

찾을 범위가 고정돼야 하기 때문에 절대참조 형식으로 지정해야 합니다.

 

1900-01-01은 숫자 1인데, 표시형식이 날짜로 되어 있어 1900-01-01로 표시될 뿐입니다.

 

(2) 열수 구하기

행을 구하는 것처럼

=MATCH(N2,$B$2:$F$400,0)라고 하면

#N/A 에러가 발생합니다.

 

 

열 별로 비교해야 하는데, 행열로 되어 있는 범위를 지정하니 에러가 발생하는 것입니다.

=MATCH(N2,$B$2:$F$2,0)라고 하면 1이 구해집니다.

 

따라서, if함수를 이용해

=IF(N2=$B$2:$F$2,COLUMN($B$2:$F$400))라고 하면

배열로 2, False, False, False, False 값이 반환됩니다.

 

따라서, Max 값을 구하면 됩니다.

=MAX(IF(N2=$B$2:$F$400,COLUMN($B$2:$F$400)))

N2=$B$2:$F$2에서 F2를 넉넉하게 F400으로 수정했습니다.

 

그런데 2가 아니라 1이 돼야 하므로 -1을 하면 됩니다.

 

(3) 원하는 날짜 구하기 1

=OFFSET(B1,MATCH(M2,$A$2:$A$400,0),MAX(IF(N2=$B$2:$F$400,COLUMN($B$2:$F$400))-1))라고 하면

원하는 값 "가1"이 구해질 줄 알았는데, cols가 0부터 시작하기 때문입니다.

 

우리는 원하는 날짜인 2024-01-31을 구해야 하기 때문에 4를 더해서 -1을 3으로 수정하면 됩니다.

 

참조셀을 G1로 지정하면 다시 0이 돼야 하기 때문에 -2를 해야 합니다.

=OFFSET($G$1,MATCH(M2,$A$2:$A$400,0),MAX(IF(N2=$B$2:$F$400,COLUMN($B$2:$F$400))-2))

 

G1셀을 아래로 내려갈 때 변하면 안 되므로 절대참조형식으로 바꿨습니다.

 

(4) 원하는 날짜 구하기 2

열 수 구할 때 if를 사용할 수도 있지만

* 연산자를 이용해 구할 수도 있습니다.

=OFFSET($G$1,MATCH(M2,$A$2:$A$400,0),MAX((N2=$B$2:$F$400)*COLUMN($B$2:$F$400))-2)

 

4. Index 함수 이용

가. Index 함수의 구문

INDEX(array, row_num, [column_num])로

Offset 함수와 비슷한데 row_num과 column_num이 1부터 시작하는 점만 다릅니다.

 

나. 배열

배열은 날짜가 있는 $G$2:$K$400를 절대참조 형식으로 지정합니다.

Offset 함수를 이용할 때는 0부터 시작하기 때문에 G1셀로 지정한 것과 다릅니다.

 

다. 행 수 구하기

MATCH(M2,$A$2:$A$400,0)로 동일합니다.

 

라. 열 수 구하기

비슷하게  MAX((N2=$B$2:$F$400)*COLUMN($B$2:$F$400))-1로 구하는데,

0이 아니라 1부터 시작하므로 -1로 수정해야 합니다.

 

마. 원하는 날짜 구하기

위 수식을 모두 연결하면

=INDEX($G$2:$K$400,MATCH(M2,$A$2:$A$400,0),MAX((N2=$B$2:$F$400)*COLUMN($B$2:$F$400))-1)

가 됩니다.

 

이제 O2셀과 P2셀을 선택한 다음 P2셀의 채우기 핸들을 더블 클릭하면

4행까지 수식이 채워지고, 값을 확인하면 맞습니다.

 

 

index,match(완성).xlsx

0.01MB