Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe een waarde opzoeken en de cel boven of onder retourneren in Excel?

In Excel gebruiken we de functie VERT.ZOEKEN om een ​​specifieke waarde uit een bereikgegevens te vinden, maar weet u hoe u een waarde opzoekt en vervolgens de waarden boven of onder retourneert? Eigenlijk kunt u de INDEX-functie gebruiken om het af te handelen.

Zoek een waarde op en retourneer de cel boven of onder

pijl blauw rechts bel Zoek een waarde op en retourneer de cel boven of onder

Zoek een waarde op en retourneer cel hierboven

Selecteer een lege cel waarin u de retourwaarde wilt plaatsen en typ deze formule =INDEX(A1:A8,MATCH(D1,A1:A8,0)-1,1), druk op Enter toets om de waarde te retourneren. Zie screenshot:
doc kijk retourcel boven 1

Zoek een waarde op en retourneer de cel hieronder

Selecteer een lege cel waarin u de retourwaarde wilt plaatsen en typ deze formule =INDEX(A1:A8,MATCH(D1,A1:A8,0)+1,1), druk op Enter sleutel om het resultaat te krijgen. Zie screenshot:
doc kijk retourcel boven 2

Opmerking:: in de formules is de eerste A1: A8 het bereik waar u naar waarde zoekt, en de tweede A1: A8 is het bereik waar u het criterium wilt opzoeken, D1 is de waarde die u opzoekt, 1 geeft de kolomnummer dat u wilt retourneren.

Als u een waarde wilt opzoeken en hieronder en de 3 cellen rechts van de verwijzing wilt terugkeren, kunt u deze formule toepassen =INDEX(F1:H8,MATCH(K1,F1:F8,0)+1,3).
doc kijk retourcel boven 3

Tip.Als u snel een waarde wilt opzoeken en in een andere kolom wilt terugkeren, probeer dan de Kutools voor Excel's te gebruiken Zoek een waarde in de lijst zoals weergegeven in de volgende schermafbeelding. Het is volledig functioneel zonder beperking in 60 dagen, download en maak nu een gratis proefversie.
doc kijk retourcel boven 4

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Josh · 1 months ago
    Anyone able to answer John Chapman's question from four years ago? I have similar challenge. My current formula is =COUNTIFS(G2:G16482,"<2",I2:I16482,F16490)
    but I want to count the occurence of the value in cell F16490 in the row above for the range I2:I16482.
  • To post as a guest, your comment is unpublished.
    Brian · 2 months ago
    Hello, This is useful for the row above or below but I would like to alternate the lookup sometimes +1 to +3 or - 1 to -3. Can I make one formula that uses an input cell reference so I can type in an offset? 
  • To post as a guest, your comment is unpublished.
    Rich · 1 years ago
    I have multiple rows with "Name" in the row just above the cell with a person's name in it. The formula works great for just one name but I have a few thousand names. How do I convert the formula so it will find each name and bring them into a separate column?
  • To post as a guest, your comment is unpublished.
    Zach · 1 years ago
    How can I get the sum =(INDEX($I:$L,MATCH($S$2,$L:$L,0)-1,1)) Currently it pulls the data of the first time my match happens. I'm looking to get the sum of all the times the match happens. The time data is in I.
    • To post as a guest, your comment is unpublished.
      Grega · 1 years ago
      Use the sumif statement for that
  • To post as a guest, your comment is unpublished.
    Ningo · 1 years ago
    Hi, I'm trying to get this working by looking up values in a row rather than a column. Please assist
    • To post as a guest, your comment is unpublished.
      Terry · 1 years ago
      Make sure you also switch the column and area number positions.
      instead of
      =INDEX(F1:H8,MATCH(K1,F1:F8,0)+1,1)
      do this
      =INDEX(F1:H8,2,MATCH(K1,F1:F8,0))

    • To post as a guest, your comment is unpublished.
      Charles · 1 years ago
      Maybe try changing your range from a vertical range to a horizontal range. For example, F1:F8, do you see how thats a vertical range? Try changing it to a horizontal range like A1:H1. Do you see how that's now a horizontal range? So anywhere there was a vertical range, replace it with your horizontal range. I don't know if it will work, just an idea.
  • To post as a guest, your comment is unpublished.
    joanne · 1 years ago
    I want to create a formula to show an error if the number in B column and C column and I want to put the formula in A column that will show as an error if the number entered in column B if below 712 and column C above 812?
  • To post as a guest, your comment is unpublished.
    Alex · 1 years ago
    Hey i was wondering if there was a way to get this to work so when you pull down the formula to another cell it increases how many rows you want it to go down.
  • To post as a guest, your comment is unpublished.
    Trey · 2 years ago
    How can I get this to work for my entire workbook. Trying to search by purchase order and list the container number that is one line above.

    =INDEX('Thur 9.5:Tues 12.31'!,MATCH('Hot Containers'!A5,'Thur 9.5:Tues 12.31'!,0)-1,1)
  • To post as a guest, your comment is unpublished.
    Ginny · 2 years ago
    Hello,

    How can I use this formula to match data in cell A3 in columns A to H and rows 1 to 9 and return the value in the row below. I have tried this formula =INDEX(Sheet1!a1:h9,MATCH(A3,Sheet1!a1:h9,0)+1,1) but it didn't work. Oh yeas and its data from another sheet.

    Any help would be greatly appreciate.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Hi, you need to change =INDEX(Sheet1!a1:h9,MATCH(A3,Sheet1!a1:h9,0)+1,1) to =INDEX(Sheet1!a1:h9,MATCH(A3,Sheet1!a1:a9,0)+1,1), or you can use this formula =INDEX(A1:A9,MATCH(A3,A1:A9,0)+1,1)
  • To post as a guest, your comment is unpublished.
    Jeyner Lopez · 2 years ago
    Hi, how to know how many times a value occur after certain value, example I have a list of numbers, I try to know how many time number 2 is after 1 and so on
  • To post as a guest, your comment is unpublished.
    ryan · 2 years ago
    I have a piece of text in cell A2 in spreadsheet A. In spreadsheet B I have the same text in cell A4 and in cell C4 I wish to use a formula that will return the value of cell B4 from spreadsheet A. Is this possible?
  • To post as a guest, your comment is unpublished.
    ryan · 2 years ago
    I have a piece of text in cell A2 in spreadsheet A. In spreadsheet B I have the same text in cell A4 and in cell C4 I wish to use a formula that will return the value of cell B4 from spreadsheet A. Is this possible?
  • To post as a guest, your comment is unpublished.
    Jim · 3 years ago
    I need this formula, but with multiple columns
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      What's you need? You can describ your quetion with more details so that we can help you.
  • To post as a guest, your comment is unpublished.
    Mike · 3 years ago
    Lets say my names run across row 1 and the values directly below in row 2. How do I look it up then? This formula doesn't seem to work when the data is horizontal.
    • To post as a guest, your comment is unpublished.
      simon · 3 years ago
      I looked around quite a bit and found this:

      =INDEX(A1:Z1,MATCH(D3,A10:Z10,0))

      D3 is your lookup value; row 10 has the values you are searching; row 1 has the values you will return where there's a match in the relevant column in row 10 with D3
  • To post as a guest, your comment is unpublished.
    Robin · 3 years ago
    Hello! I have an interesting question. Let’s say that i have a letter(”S” for example) on every row but in different places. So on the second row the letter ”S” can be found in cell X2 and on the third row the letter ”S” can be found in cell F3. At the top of the table (the first row) you have dates, ranging horizontilly. What function should I use in ordern to find the letter ”S” on each row, and then retreive the date that is located x amount of steps above it? How do you search horizontelly for a letter, and when you find that specific letter, go up to row 1 to retreive its date? I hope I explained this in a clear manner :)
  • To post as a guest, your comment is unpublished.
    Jon Chapman · 4 years ago
    This is a great formula. Can it be wrapped in to a COUNTIF formula, so instead of returning the looked-up value, it counts how many times the result was found? EG: how many times 'Jack' was returned
    • To post as a guest, your comment is unpublished.
      Sunny · 4 years ago
      Sorry, I did not get ur question clearly. If you want to count the times a word appears in a range? If so, you can use this formula =COUNTIF(A2:A12,"Jack"), you can go to this atricle https://www.extendoffice.com/documents/excel/3224-excel-count-number-of-occurrences-of-a-word-in-a-column.html for more details and methods.