+ Reply to Thread
Results 1 to 10 of 10

localizar valor entre intervalo de 2 valores

  1. #1
    Registered User
    Join Date
    02-19-2019
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    6

    localizar valor entre intervalo de 2 valores

    Bom dia!

    Tenho uma planilha onde uma coluna A "Nota Fiscal" contem as NF A, B, C, D, E. e na coluna B e C tem o Valor "MINIMO" e "MAXIMO" EX: NF A de 1 a 10, NF B de 11 a 20, NF C de 21 a 30.... e tenho uma coluna com números aleatórios 7, 9, 20, 31, 41, 16, 24, 19.

    Gostaria de saber uma formula que me mostre qual a Nota Fiscal desses números aleatórios uma vez que pra localizar eles terei que saber se ele estão entre o valor MINIMO E MAXIMO.

    EXCEL.JPG

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,448

    Re: localizar valor entre intervalo de 2 valores

    This by no means is the final answer, it will just show others what your question is.

    In G2 enter this, and drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,172

    Re: localizar valor entre intervalo de 2 valores

    May be

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or in pt
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the file
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,701

    Re: localizar valor entre intervalo de 2 valores

    Maybe:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    359

    Re: localizar valor entre intervalo de 2 valores

    Los siento ....mi espanol is mal! If you can put "Nota Fiscal" in Column D it would make things easier. Then you could use the Vlookup formula. in G2 put the formula
    Please Login or Register  to view this content.
    . This formula is NOT looking for an exact match and it worked for me. Otherwise, you may need to use an Index Match formula?
    Bueno suerte.
    Attached Files Attached Files

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,701

    Re: localizar valor entre intervalo de 2 valores

    As well as requiring the data to be restructured, VLOOKUP as described, does not cater for numbers below or above the minimum/maximum values in the table.

  7. #7
    Registered User
    Join Date
    02-19-2019
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    6

    Re: localizar valor entre intervalo de 2 valores

    Deu Certo, Obrigado

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,701

    Re: localizar valor entre intervalo de 2 valores

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  9. #9
    Registered User
    Join Date
    02-19-2019
    Location
    brasil
    MS-Off Ver
    excel 2016
    Posts
    6

    Re: localizar valor entre intervalo de 2 valores

    A solução deu certo parcial pois se eu colocar um numero maior que 50 ele irá trazer a Nota fiscal E, sendo que não tem nenhuma nota atrelada pra esse número. teria uma forma de impedir de fazer isso?

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,701

    Re: localizar valor entre intervalo de 2 valores

    See post #4

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Distribuição de Valor
    By Renata2012 in forum Non English Excel
    Replies: 0
    Last Post: 06-23-2013, 08:30 AM
  2. Problema na Contagem de Valor
    By sgps in forum Excel General
    Replies: 1
    Last Post: 08-22-2006, 01:25 PM
  3. [SOLVED] Cambiar color fila dependiendo de valor de columna
    By angelajc@gmail.com in forum Excel General
    Replies: 1
    Last Post: 03-02-2006, 08:55 AM
  4. Replies: 1
    Last Post: 01-27-2006, 06:15 PM
  5. Usando una Imagen para Ver Valores de Celda en Otras Hojas
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-28-2005, 09:29 AM
  6. Ocultando la Visualización de los Valores Cero
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-28-2005, 04:03 AM
  7. [SOLVED] como encuentro un valor de una tabla dinamica con buscarv?
    By Ger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2005, 04:05 AM
  8. ocutar,fixar valores
    By Juninho PUC-Minas in forum Excel General
    Replies: 1
    Last Post: 02-28-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1