vlookup?
 

I have the following 3 columns of data
000 0.198407405 PO3
001 0.207502916 CV3
002 0.984589896 CV3
003 0.715903627 FB2
It goes a lot further. I want to create a formula for another column that
will find a data point out of column 3, but enter the corresponding data from
column 1. ie: find FB2 in Column 3, but 003 is outcome.

vlookup?
 

try this

put this formula in F2

=INDEX(A2:A8,MATCH(E2,C2:C8,0),0)




On Nov 16, 12:56*am, Bill wrote:
I have the following 3 columns of data
000 * * 0.198407405 * * PO3
001 * * 0.207502916 * * CV3
002 * * 0.984589896 * * CV3
003 * * 0.715903627 * * FB2
It goes a lot further. *I want to create a formula for another column that
will find a data point out of column 3, but enter the corresponding data from
column 1. *ie: *find FB2 in Column 3, but 003 is outcome.

vlookup?
 

That works. What about mutiple occurrences?

"muddan madhu" wrote:

try this

put this formula in F2

=INDEX(A2:A8,MATCH(E2,C2:C8,0),0)




On Nov 16, 12:56 am, Bill wrote:
I have the following 3 columns of data
000 0.198407405 PO3
001 0.207502916 CV3
002 0.984589896 CV3
003 0.715903627 FB2
It goes a lot further. I want to create a formula for another column that
will find a data point out of column 3, but enter the corresponding data from
column 1. ie: find FB2 in Column 3, but 003 is outcome.

vlookup?
 

taking your example

in E2 you have CV3 and you need 001 in F2
in E3 you have CV3 and you need 002 in F3

in F2 put this formula ( use Ctrl + Shift + Enter )

=IF(ISERROR(INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$C$ 4,ROW($A$1:$A$4)),ROW
(1:1)),0)),"",INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$ C$4,ROW($A$1:$A
$4)),ROW(1:1)),0))



On Nov 16, 1:21*am, Bill wrote:
That works. *What about mutiple occurrences?

"muddan madhu" wrote:
try this

put this formula in F2

=INDEX(A2:A8,MATCH(E2,C2:C8,0),0)

On Nov 16, 12:56 am, Bill wrote:
I have the following 3 columns of data
000 * * 0.198407405 * * PO3
001 * * 0.207502916 * * CV3
002 * * 0.984589896 * * CV3
003 * * 0.715903627 * * FB2
It goes a lot further. *I want to create a formula for another column that
will find a data point out of column 3, but enter the corresponding data from
column 1. *ie: *find FB2 in Column 3, but 003 is outcome.

vlookup?
 

change the ranges according to your need.

=IF(ISERROR(INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$C$ 10,ROW($A$1:$A
$10)),ROW
(1:1)),0)),"",INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$ C$10,ROW($A$1:$A
$10)),ROW(1:1)),0))



On Nov 16, 1:38*am, muddan madhu wrote:
taking your example

in E2 you have CV3 and you need 001 in F2
in E3 you have CV3 and you need 002 in F3

in F2 put this formula *( use Ctrl + Shift + Enter )

=IF(ISERROR(INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$C$ 4,ROW($A$1:$A$4)),ROW
(1:1)),0)),"",INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$ C$4,ROW($A$1:$A
$4)),ROW(1:1)),0))

On Nov 16, 1:21*am, Bill wrote:

That works. *What about mutiple occurrences?

"muddan madhu" wrote:
try this

put this formula in F2

=INDEX(A2:A8,MATCH(E2,C2:C8,0),0)

On Nov 16, 12:56 am, Bill wrote:
I have the following 3 columns of data
000 * * 0.198407405 * * PO3
001 * * 0.207502916 * * CV3
002 * * 0.984589896 * * CV3
003 * * 0.715903627 * * FB2
It goes a lot further. *I want to create a formula for another column that
will find a data point out of column 3, but enter the corresponding data from
column 1. *ie: *find FB2 in Column 3, but 003 is outcome.

vlookup?
 

I can not get this to work. I have the 3 columns of data in columns "m",
"n", and "o". There are 216 rows starting in row 1. From the example below,
I will create in column "r", the heading FB2. Under the heading, I want to
see each 3 digit number that corresponds to FB2, with out repeating the same
number.

"muddan madhu" wrote:

taking your example

in E2 you have CV3 and you need 001 in F2
in E3 you have CV3 and you need 002 in F3

in F2 put this formula ( use Ctrl + Shift + Enter )

=IF(ISERROR(INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$C$ 4,ROW($A$1:$A$4)),ROW
(1:1)),0)),"",INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$ C$4,ROW($A$1:$A
$4)),ROW(1:1)),0))



On Nov 16, 1:21 am, Bill wrote:
That works. What about mutiple occurrences?

"muddan madhu" wrote:
try this

put this formula in F2

=INDEX(A2:A8,MATCH(E2,C2:C8,0),0)

On Nov 16, 12:56 am, Bill wrote:
I have the following 3 columns of data
000 0.198407405 PO3
001 0.207502916 CV3
002 0.984589896 CV3
003 0.715903627 FB2
It goes a lot further. I want to create a formula for another column that
will find a data point out of column 3, but enter the corresponding data from
column 1. ie: find FB2 in Column 3, but 003 is outcome.

vlookup?
 

See your later post

--
Biff
Microsoft Excel MVP


"Bill" wrote in message
...
I can not get this to work. I have the 3 columns of data in columns "m",
"n", and "o". There are 216 rows starting in row 1. From the example
below,
I will create in column "r", the heading FB2. Under the heading, I want
to
see each 3 digit number that corresponds to FB2, with out repeating the
same
number.

"muddan madhu" wrote:

taking your example

in E2 you have CV3 and you need 001 in F2
in E3 you have CV3 and you need 002 in F3

in F2 put this formula ( use Ctrl + Shift + Enter )

=IF(ISERROR(INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$C$ 4,ROW($A$1:$A$4)),ROW
(1:1)),0)),"",INDEX($A$1:$A$10,SMALL(IF($E2=$C$1:$ C$4,ROW($A$1:$A
$4)),ROW(1:1)),0))



On Nov 16, 1:21 am, Bill wrote:
That works. What about mutiple occurrences?

"muddan madhu" wrote:
try this

put this formula in F2

=INDEX(A2:A8,MATCH(E2,C2:C8,0),0)

On Nov 16, 12:56 am, Bill wrote:
I have the following 3 columns of data
000 0.198407405 PO3
001 0.207502916 CV3
002 0.984589896 CV3
003 0.715903627 FB2
It goes a lot further. I want to create a formula for another
column that
will find a data point out of column 3, but enter the corresponding
data from
column 1. ie: find FB2 in Column 3, but 003 is outcome.