Look into an array and return the same row in another column

**cmt_london** · 11-09-2007, 09:18 AM

Hi all,

I have an array with a lot of rows and columns filled with different codes.

I need to look into this array for one specific number and return the value in the same row in an specific column.

I can't use Vlookup in this case because it needs to have the values you are looking for in the first column and my values are spread in different columns.

Thanks in advance.

**NBVC** · 11-09-2007, 09:21 AM

You can use Index/Match as an alternative to Vlookup that will look left or right of the lookup column.

e.g. =Index(A:A,Match(X1,C:C,0))

where X1 is the value to find in column C and return the corresponding data from column A

**cmt_london** · 11-09-2007, 11:20 AM

Thanks for the reply NBVC.

But the problem I have is I have to look for in an array, not in a column and Vlookup or the formula you provided me with don't work. To use these formulas you have to have the values in one column and I have them spread in an array.

Thanks anyway, I've learnt another way to do it.

**NBVC** · 11-09-2007, 12:20 PM

Originally Posted by cmt_london

Thanks for the reply NBVC.

But the problem I have is I have to look for in an array, not in a column and Vlookup or the formula you provided me with don't work. To use these formulas you have to have the values in one column and I have them spread in an array.

Thanks anyway, I've learnt another way to do it.

Perhaps its best if posted an example (a zipped XL 2003 or earlier file) showing what exactly your are trying to do and what results you expect..

**cmt_london** · 11-09-2007, 12:51 PM

Find attached the example file.

There is an array A1:D4 with all the numbers and codes.

And there is a list of numbers A8:A16.

I need a formula in B8 that look for the number 810844 in the array A1:D4 and return the value in the column D of the same row, in this case AAA.

And the same for the cells B9 to B16.

Do you understand where I get stuck? Your formula or the Vlookup one will work if all numbers will be in the same column, but not if they are in different columns.

Thanks,