| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare arrays
Welcome, thanks for feeding back.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mavxob" wrote in message ... Thanks, this works as expected ! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare arrays | Excel Worksheet Functions | |||
| How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
| two arrays | New Users to Excel | |||
| Arrays | Excel Worksheet Functions | |||
| Arrays | Setting up and Configuration of Excel | |||
Linear Mode
