Welcome Guest
Log In | Register )
You last visited December 3, 2016, 8:56 pm
All times shown are
Eastern Time (GMT-5:00)

excel help

Topic closed. 2 replies. Last post 10 years ago by budward.

Page 1 of 1
PrintE-mailLink
lottaloot's avatar - AvatarZ56
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
Posted: December 18, 2006, 3:05 pm - IP Logged

621
532
249

I have these numbers located in column A (1:3)
Can anyone help me with a formula that will return my search result (in these case 532) as well as the information immediately above & below the location of cell that has my search info?

L ttaL   T

    Avatar

    United States
    Member #41846
    June 23, 2006
    458 Posts
    Offline
    Posted: December 18, 2006, 7:50 pm - IP Logged

    lottaloot  I'm not sure I uderstand the question.  Are you using the find function to do the search?

    Are all three digits of each number in one cell?   If they are then find by column and value will find each occurance and show what is immediately before and after.  if the search box is in the way just drag it to another part of the screen.

    if each digit is in a seperate cell this will not work.  If you are using some other search method you will need to be more specific.

    I believe in a previous post you said you were using 2003 version  this may have functions I'm not aware of.

    regards

    p8 

      Avatar

      United States
      Member #33295
      February 19, 2006
      699 Posts
      Offline
      Posted: December 19, 2006, 7:36 am - IP Logged

      621
      532
      249

      I have these numbers located in column A (1:3)
      Can anyone help me with a formula that will return my search result (in these case 532) as well as the information immediately above & below the location of cell that has my search info?

       This will work.It will let you type a number in B1 and search Column A for that number. Will return not found if the number isn't in your list. 

       

      =IF(ISERROR(MATCH($B$1,A:A,0)),"not found",INDEX(A:A,MATCH($B$1,A:A,0)-1))
      =IF(ISERROR(MATCH($B$1,A:A,0)),"not found",INDEX(A:A,MATCH($B$1,A:A,0)))
      =IF(ISERROR(MATCH($B$1,A:A,0)),"not found",INDEX(A:A,MATCH($B$1,A:A,0)+1))

      Isabel, you are going to feel very silly when this turns out to be make-believe.