look in coulm

[M.K]

how i can do the following in excel.
if i have the following
abc 77
fgh 54
ijk 77
alm 55
zax 77
fdt 23

I want funcation to look B1 in B:B and write from A:A
for example look for 77 write abc but if 77 more than times i want to write
the second 77 so in my example it will write ijk

 

[T. Valko]

Try this array formula** :

=INDEX(A:A,SMALL(IF(B2:B7=77,ROW(B2:B7)),IF(COUNTIF(B2:B7,77)>1,2,1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[M.K]

Thank you for your reply but its not work
also i dont want to spesif number but cell for example in your formula you
spesific 77 but i want to write B1 insted if 77

is it possible??

 

[Dave Peterson]

Try replacing 77 with B1 and see what happens.

(Remember that it's still an array formula.)

 

[M.K]

the result will be ijk

Try replacing 77 with B1 and see what happens.

(Remember that it's still an array formula.)

 

[Dave Peterson]

I don't understand what that means.

the result will be ijk

 

[M.K]

I mean if i have the following data

abc 77
deo 56
eeg 77
ghi 43
jklm 77
def 24

and i hav in other sheet another coulm with following data


77
56
30

if i use vlookup to see A1=77 (second sheet) from first sheet the result
will be jklm (last record) but i want to get second record which eeg

is it possible??

 

[Dave Peterson]

Biff's formula worked perfectly for me.

How did you adjust it to point at ranges on different sheets?

 

[herochenna]

Visit www.ExcelGoodies.Com