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

[cmt_london]

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]

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]

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]

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]

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,

[NBVC]

The file didn't attach...

make sure it is zipped and that you click Upload after you select the file to attach.

[cmt_london]

Can you see it now?

[NBVC]

okay...i'll take a chance and hope that I know what you are looking for....

see attached.

formula in B8 copied down is:

Please Login or Register  to view this content.

this will look for the number in A8 within the array A1:C4 and return the value in column D that corresponds with the row number the match is found in.

Is this what you were after?

[cmt_london]

NVBC that's exactly what I needed.

Thanks very much.

[cmt_london]

Hi NVBC,

I manage to get your formula working when the values in the array $D$1:$D$4 (where the returning values are) are typed numbers.

Actually I've got these values as a formula like this:

LEFT(A1,6)

Because I've got those codes in the column A with more caracters.

The thing is your formula works if I type the values manually for the array $D$1:$D$4 but if I populate this array with LEFT() formula it doesn't.

Do you know why it could be?

Thanks for all your supporting.

[NBVC]

If those formulas are in D1:D4, the indexed array, it should work properly and return the values no matter if you type them in directly or if you use a formula...

...however, Left(A1,6) does return a text string which will not be the same as an entry that is entered as a number (General format)....

so try changing your =Left(A1,6) formula to =Left(A1,6)+0 this will convert the string to a real number....

Hopefully that helps.