How do i return data in a cell from the referred cell?
 

ok i have used vlookup and its like

=VLOOKUP(B26,B2:AF23,B2:AF2,FALSE)

where B26 refers to a value and its ok

but Table_array shows an error that its referring to an empty cell when
that cell contains data not functions but data.. and i need it to show me the
data in that cell, but i dont know what to specify.. plz help

exactly i need to know an another value from the same row and when i looked
up help i founf vlookup does it but i cant do it..

i'll explain in detail,

i wish to see the max value from a row and then in another cell i wish to
see an another value from the column of the result of max value.

for eg: B26 has function =MAX(B23:AF23) and it results 1157 (1157 is on P23)
then in B27 i wish to see the data from column P2 from the same column of
the results of b26

so i input =VLOOKUP(B26,B2:AF23,B2:AF2,FALSE) in B27

hope am clear and am i using the right function to view the data from the
same column refering to B26

How do i return data in a cell from the referred cell?
 

Try this:

=INDEX(B2:AF2,MATCH(B26,B23:AF23,0))

--
Biff
Microsoft Excel MVP


"SunnyWantsome" wrote in message
...
ok i have used vlookup and its like

=VLOOKUP(B26,B2:AF23,B2:AF2,FALSE)

where B26 refers to a value and its ok

but Table_array shows an error that its referring to an empty cell when
that cell contains data not functions but data.. and i need it to show me
the
data in that cell, but i dont know what to specify.. plz help

exactly i need to know an another value from the same row and when i
looked
up help i founf vlookup does it but i cant do it..

i'll explain in detail,

i wish to see the max value from a row and then in another cell i wish to
see an another value from the column of the result of max value.

for eg: B26 has function =MAX(B23:AF23) and it results 1157 (1157 is on
P23)
then in B27 i wish to see the data from column P2 from the same column of
the results of b26

so i input =VLOOKUP(B26,B2:AF23,B2:AF2,FALSE) in B27

hope am clear and am i using the right function to view the data from the
same column refering to B26

How do i return data in a cell from the referred cell?
 

You have a couple of errors in the way you are interpreting the VLOOKUP
function.

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

lookup_value is the value you are looking for in table_array

table_array is the range which contains the data to be found. The data you
are looking for must be in the first column of this range. Also the range
should be in absolute format. That is the $ signs before the column and row
id's otherwise when you copy the formula down or across, the table_array
changes and this is not usually the case because it is in a fixed position.

col_index_num is the number of the column in the table_array which has the
value that you want to insert. It is a number such as 3 which is column 3 of
the array. On most occasions it would be the last column of the table_array
because there is no need to have this array wider than where the required
data is.

range_lookup is simply true or false. Using false will only find an exact
match and it is not necessary for the table_array to be sorted. Using true
will give the next largest value and the table_array must be sorted.

Example of the formula. A26 contains the value to lookup.

$B$2:$AF$23 is the table_array

31 is the column number in the table_array which has the data to insert.

false only an exact match.

=VLOOKUP(A26,$B$2:$AF$23,31,FALSE)

Regards,

OssieMac


"SunnyWantsome" wrote:

ok i have used vlookup and its like

=VLOOKUP(B26,B2:AF23,B2:AF2,FALSE)

where B26 refers to a value and its ok

but Table_array shows an error that its referring to an empty cell when
that cell contains data not functions but data.. and i need it to show me the
data in that cell, but i dont know what to specify.. plz help

exactly i need to know an another value from the same row and when i looked
up help i founf vlookup does it but i cant do it..

i'll explain in detail,

i wish to see the max value from a row and then in another cell i wish to
see an another value from the column of the result of max value.

for eg: B26 has function =MAX(B23:AF23) and it results 1157 (1157 is on P23)
then in B27 i wish to see the data from column P2 from the same column of
the results of b26

so i input =VLOOKUP(B26,B2:AF23,B2:AF2,FALSE) in B27

hope am clear and am i using the right function to view the data from the
same column refering to B26