look in coulm

  • Thread starter Thread starter M.K
  • Start date Start date
M

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
 
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.
 
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??
 
Try replacing 77 with B1 and see what happens.

(Remember that it's still an array formula.)
 
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??
 
Biff's formula worked perfectly for me.

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