vlookup?

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

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.
 
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))
 
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))
 
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.
 
Back
Top