복수의 열에서 VLOOKUP 적용

성명	주민번호
김1	111111-1111111
김2	111111-1111112
김3	111111-1111113
김4	111111-1111114
김5	111111-1111115
김6	111111-1111116

이런식의 구조로 된 표에서 사람이름을 주면 주민번호를 찾는 방법은 vlookup을 쓰면 간단히 해결된다. 

하지만, 데이터가 좌우로 넓게 퍼져있다면?

성명	주민번호	성명	주민번호	성명	주민번호
김1	111111-1111111	이1	222222-2222222	박1	333333-3333333
김2	111111-1111112	이2	222222-2222223	박2	333333-3333334
김3	111111-1111113	이3	222222-2222224	박3	333333-3333335
김4	111111-1111114	이4	222222-2222225	박4	333333-3333336
김5	111111-1111115	이5	222222-2222226	박5	333333-3333337
김6	111111-1111116	이6	222222-2222227	박6	333333-3333338

즉, 위와 같은 식으로 한다면 어떻게 vlookup을 적용할 수 있을까?

if구문을 중첩해서 해결할 수도 있을 것이다. 

첫열에서 에러나면 둘째열, 둘째열에서도 에러나면 세째열에서 vlookup을 적용하는 식으로.....

지식인에서 신묘한 답이 있어 분석해본다.

첨부화일을 열어서 같이 보면 이해가 더 쉽다

VLOOKUP 다수열 적용.xlsx

=IFERROR(VLOOKUP(A4,OFFSET(F:G,,MAX(COUNTIF(OFFSET(F$2:F$31,,{0,2,4}),A4)*{0,2,4})),2,0),"")

B4셀의 수식을 살펴본다.

이 부분이 아주 중요하다. offset 함수에서 row 부분은 빈칸이니 행 방향으로는 이동하지 않는다

column부분에 숫자가 들어있으면 그만큼 이동하는 것인데

지금은 {0,2,4} 가 들어가 있다.

원래는 숫자 하나가 들어가야 하는 자리에 배열이 들어가 있으니 수식계산 상으로는 오류가 난다. 

하지만, 지난 번에도 봤듯이, 수식계산에서 오류가 난다고 해서 이후에 계산이 안되는 것은 아니다. 

미리 말하면,  배열을 column위치에 줌으로써 

OFFSET(F$2:F$31,,{0,2,4}) 의 결과는

F$2:F$31에서 0만큼, 2만큼, 4만큼 이동한 F$2:F$31, H$2:H$31, J$2:J$31 범위 모두를 지정하는 것이 된다.

이 기능으로 인해 여러 열에 걸쳐 vlookup을 적용하는 것이 가능해진다. 

이제 위의 수식에서 밑줄친 부분은 

F$2:F$31, H$2:H$31, J$2:J$31 의 범위에서 "이4"라는 이름이 있는지를 확인하는 countif함수가 된다.

원래 countif함수의 첫번째 인수인 범위는 단일 범위를 주어야하고 지금처럼 복수의 범위를 주면 에러가 난다.

하지만 지금은 괜찮다. 그 이유는 모르겠다.

아마 {0,2,4}의 배열을 넣음으로써 보통의 상황과는 다르게, 마치 배열수식처럼 작동하는 것이 아닌가 추측한다.

 

F$2:F$31, H$2:H$31, J$2:J$31 의 범위에서 "이4"라는 이름이 있는지 확인해보면

H$2:H$31의 범위에 "이4" 가 있고 다른 곳에는 없으므로

{0,1,0}이라는 배열이 값으로 나온다.

여기에 {0,2,4}라는 배열을 곱한다.

이전에 엑셀에서 배열의 곱셈은 수학에서 행렬의 곱셈과는 다르다고 했다. 

지금처럼 같은 크기의 범위끼리 곱하게 되면, 자신과 같은 위치에 있는 값하고만 곱셈을 하게 된다

그래서 {0,1,0}과 {0.2.4}를 곱하면 {0.2.0}이 되고, 여기서 최대값을 구하면 2가 된다.

이 부분은 사실, F$2:F$31, H$2:H$31, J$2:J$31 중에서 vlookup을 적용할 범위가 어디인지를 정하기 위한 것이다. 

숫자가 하필 {0,2,4}인 것은 기준점인 F:G에서 얼마만큼 떨어져있는지를 나타내기 위함이니까,

나중에 범위가 달라지면 그에 맞게 이 부분을 수정하면 된다

그래서 결국 OFFSET(F:G,,2)라는 수식이 되었다.

위에서 OFFSET(F$2:F$31,,{0,2,4}) 의 수식에서, column의 값으로 단일 숫자가 아니라 배열을 준 것도 놀라웠는데

지금도 기준점이 단일 셀이나 단일 열이 아니라 F:G 와 같은 여러 열이 기준점이다. 이렇게도 가능하다는 데 놀랍다.

아무튼, 지금 밑줄친 수식은 "F:G 범위에서 열 방향으로 2만큼 이동한 범위"를 가리키므로

결국 H:I 가 결과로 도출된다. 

이후는 기본적인 vlookup함수 구문이므로 설명 생략 가능하다.

요약하면,

OFFSET함수의 몰랐던 기능 두 가지

1. OFFSET(F$2:F$31,,{0,2,4}) 처럼 row나 column의 인수로 배열을 줄 수 있다. (물론 다른 수식 안에서만 작동)

2. OFFSET(F:G,,2) 처럼 기준점을 단일 셀이나 단일 행,열이 아니라 범위를 기준점으로 줄 수 있다.