Compare arrays
 

Hello,
I need to compare a column with a set of columns and get the column number
that match the lookup column. Can somebody help? thanks!

lookup column:
1
2
3

A B C D E F
2 4 5 6 1 5
5 2 3 2 2 4
1 2 5 1 3 6

expected result is 5 (col E)

Compare arrays
 

mavxob,

Assuming you will only have one match, with the 'lookup column' in J1:J3

=SUMPRODUCT((J1=A1:F1)*(J2=A2:F2)*(J3=A3:F3)*COLUM N(A1:F1))

HTH,
Bernie
MS Excel MVP


"mavxob" wrote in message
...
Hello,
I need to compare a column with a set of columns and get the column number
that match the lookup column. Can somebody help? thanks!

lookup column:
1
2
3

A B C D E F
2 4 5 6 1 5
5 2 3 2 2 4
1 2 5 1 3 6

expected result is 5 (col E)

Compare arrays
 

Another play to try
Assuming source data in A1:F3, with "lookup col" in H1:H3
put in K1's formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=MATCH(1,(A1:F1=H1)*(A2:F2=H2)*(A3:F3=H3),0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mavxob" wrote:
Hello,
I need to compare a column with a set of columns and get the column number
that match the lookup column. Can somebody help? thanks!

lookup column:
1
2
3

A B C D E F
2 4 5 6 1 5
5 2 3 2 2 4
1 2 5 1 3 6

expected result is 5 (col E)

Compare arrays
 

Excellent ! thanks for your propmt feedback

"Bernie Deitrick" wrote:

mavxob,

Assuming you will only have one match, with the 'lookup column' in J1:J3

=SUMPRODUCT((J1=A1:F1)*(J2=A2:F2)*(J3=A3:F3)*COLUM N(A1:F1))

HTH,
Bernie
MS Excel MVP


"mavxob" wrote in message
...
Hello,
I need to compare a column with a set of columns and get the column number
that match the lookup column. Can somebody help? thanks!

lookup column:
1
2
3

A B C D E F
2 4 5 6 1 5
5 2 3 2 2 4
1 2 5 1 3 6

expected result is 5 (col E)

Compare arrays
 

Thanks, this works as expected !

"Max" wrote:

Another play to try
Assuming source data in A1:F3, with "lookup col" in H1:H3
put in K1's formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=MATCH(1,(A1:F1=H1)*(A2:F2=H2)*(A3:F3=H3),0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mavxob" wrote:
Hello,
I need to compare a column with a set of columns and get the column number
that match the lookup column. Can somebody help? thanks!

lookup column:
1
2
3

A B C D E F
2 4 5 6 1 5
5 2 3 2 2 4
1 2 5 1 3 6

expected result is 5 (col E)

Compare arrays
 

Welcome, thanks for feeding back.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mavxob" wrote in message
...
Thanks, this works as expected !