Please see the table below

A B C
1 31 34 23
2 22 27 45
3 31 12 24
4 45 14 27
5 23 45 12

How to use lookup (or any function), which looks for value only in
column C and return the value from corresponding row in column A. Say
I want to look for "27" in column C, and if i find 27 in column C, it
should return 45 from column A.

The problem with vlookup function is that if I define my Table_array
from A1:C5, it will look for 27 in range A1:C5 and will match 27 from
cell B2 first and then would return 22. If I define my Table_array
from C1:C5, it will match 27 from row 4, but cannot return anything
from Column A because vlookup cannot return the value from outside the
range of its table_array.

I hope I am able to explain my problem. Any way to overcome it
without using VBA?

Thanks
Chatur

VLOOK only looks up the item in the first row. You need to use match and
offset

Match will return the row number. The offset is one less than the row
number because the reference cell A1 in offset is 1. If match returns row 3
then the offset of row 3 from row 1 is two which is 3 - 1.

=OFFSET(A1,MATCH(E1,C1:C5)-1,0)

" wrote:

Please see the table below

A B C
1 31 34 23
2 22 27 45
3 31 12 24
4 45 14 27
5 23 45 12

How to use lookup (or any function), which looks for value only in
column C and return the value from corresponding row in column A. Say
I want to look for "27" in column C, and if i find 27 in column C, it
should return 45 from column A.

The problem with vlookup function is that if I define my Table_array
from A1:C5, it will look for 27 in range A1:C5 and will match 27 from
cell B2 first and then would return 22. If I define my Table_array
from C1:C5, it will match 27 from row 4, but cannot return anything
from Column A because vlookup cannot return the value from outside the
range of its table_array.

I hope I am able to explain my problem. Any way to overcome it
without using VBA?

Thanks
Chatur

Joel,

Thanks for your input. It worked for me however, I came across
another problem. Use of match and offset has a limitation too. If I
am not able to find exact match, I want the formula to return the
approximate match. However, to find a approximate value using match,
array should be sorted and mine is not. I cannot use the sorted array
in my database.

Is there any way it can be done?

Thanks,
Chatur



On Apr 12, 10:31 pm, Joel wrote:
VLOOK only looks up the item in the first row. You need to use match and
offset

Match will return the row number. The offset is one less than the row
number because the reference cell A1 in offset is 1. If match returns row 3
then the offset of row 3 from row 1 is two which is 3 - 1.

=OFFSET(A1,MATCH(E1,C1:C5)-1,0)

" wrote:
Please see the table below

A B C
1 31 34 23
2 22 27 45
3 31 12 24
4 45 14 27
5 23 45 12

How to use lookup (or any function), which looks for value only in
column C and return the value from corresponding row in column A. Say
I want to look for "27" in column C, and if i find 27 in column C, it
should return 45 from column A.

The problem with vlookup function is that if I define my Table_array
from A1:C5, it will look for 27 in range A1:C5 and will match 27 from
cell B2 first and then would return 22. If I define my Table_array
from C1:C5, it will match 27 from row 4, but cannot return anything
from Column A because vlookup cannot return the value from outside the
range of its table_array.

I hope I am able to explain my problem. Any way to overcome it
without using VBA?

Thanks
Chatur