VLOOKUP - Can you match columns other than column 1?

[longfisher]

Hi, folks,

I need a little help with a vlookup problem. At least, I think it's a vlookup problem.

I have 11 columns of 13 digit numbers from left to right. The numbers in the last column are in a particular sequence from top to bottom but they're not in numerical sequence.

If the number in the last column is found within any of the other 10 columns then that row of 10 numbers is of interest to me. Once I identify the row which contains a number matching that in the 11th column I need to collect all the 10 numbers on that row and display them in 10 columns to the right of the matching number in row 11.

I've tried several vlookup ideas but am hindered by the fact that vlookup needs the matching number in the first column. As I described above, the number could be in any column in the 2 dimensional array of columns and rows in columns 1 - 10.

Any help would be greatly appreciated.

LongFisher

[daddylonglegs]

Assuming your 11 columns are A to K and data starts at row 2 then use this formula in L2 copied across to U2 and down as far as necessary

=IF(ISNUMBER(MATCH($K2,$A2:$J2,0)),A2,"")

[longfisher]

Thanks for the response, but that function only works if the numbers I'm matching is found on the same row as the function.

So, let me be more clear (and sorry). Here's some numbers like my problem in columns A - D and rows 1 - 3. Column E is the number I need to match.

By the way, all the number is columns A - D are unique. There are no duplications. And, the numbers in column E should be found once and only once in the array A1 - D3. I've placed in columns F - I the results I'd like a function to yield.

A B C D E F G H I

Row1 1 2 3 4 12 9 10 11 12
Row2 5 6 7 8 6 5 6 7 8
Row3 9 10 11 12 4 1 2 3 4

Hope that helps and sorry again for the poor description.

LongFisher

[shg]

Please Login or Register  to view this content.

The array formula in H1:K1 and copied down is

=INDEX( tbl, SUMPRODUCT( (tbl = F1) * ROW(tbl) ), 0)

tbl is the named range covering A1:D3

[longfisher]

Anyone got a link to a good primer?

LongFisher

Oh, thanks everyone for the help.