Is there an Excel equivalent way to mimic pandas.DataFrame.iloc?

Admin 1
Viewed 322

Given an Excel spreadsheet with 30 rows of data that looks like:

|------|------------|
|  id  |    text    |
|------|------------|
|   1  |    apple   |
|   2  |    apple   |
|   3  |   banana   |
|   4  |    apple   |
|   5  |   orange   |
|  ... |    ...     |
|  30  |   orange   |
|------|------------|

I'm wondering if there's a way to do an integer-location based indexing. For example, if I wanted to return the first 3 rows starting from the 2nd row, I would use df.iloc[2:5]:

expected output:
|------|------------|
|   2  |    apple   |
|   3  |   banana   |
|   4  |    apple   |
|------|------------|

I would ultimately want to specify (1) the starting position and (2) the number of rows to return from the starting position, in Excel (e.g. return 10 rows starting from id=21). I've tried creating an array using INDEX but couldn't figure it out.

excel python
2 Answers

You need two functions for these:

MATCH will find a value in a range and return their position INDIRECT will allow you to specify a string representing a range, and it will return the values contained in that range.

MATCH function

INDIRECT function

Made a test like this:

enter image description here

My formula in K9 is:

=INDIRECT("A"&MATCH(L4;A:A;0)&":B"&(MATCH(L4;A:A;0)-1+L5))

This is how it works:

  1. MATCH(L4;A:A;0) will find the ID position in column A. Because I said i am searching in whole column, value returned is the row number where the ID is. So if I want to find ID 2, it will return 3.
  2. We combine the number from previous step with a string, to get a string like A3:B5. The 5 is obtained from MATCH(L4;A:A;0)-1+L5) where i get the row number of the ID I want to find, sum up the number of rows to get, and substract 1.
  3. INDIRECT("A3:B5") is what i want and it's returned.

NOTE: Depending on your Excel version, INDIRECT will auto expand to adyacent cells or not. Maybe you have to drag it manually

A video showing how it changes if i want to search different id or different number of rows:

enter image description here

NOTE 2: If the id is not found, MATCH will raise an error, and the formula will not work.

Check first if the ID exists in column A with COUNTIF and if true, then you do MATCH and INDIRECT. Something like this:

=IF(COUNTIF(A:A;L4)>0;INDIRECT("A"&MATCH(L4;A:A;0)&":B"&(MATCH(L4;A:A;0)-1+L5));"ID NOT FOUND")

Same if number of rows returned is 0 or less.

user_91998280

There are various formulas depending on your version of Excel.

If you have dynamic arrays (Excel 2016+ if I recall correctly), you can enter the following in a single cell. If your Excel does not have dynamic arrays, a modification would be necessary.

=INDEX(fruitTbl,Start,0):INDEX(fruitTbl,Count+Start-1,0)

I converted the data range to an Excel Table, but you can use regular addressing if you prefer. With a Table, the first row of data would be 1 since Table returns only the data body range.

enter image description here

to try if you do not have dynamic arrays

Enter in some cell:

=IFERROR(INDEX(INDEX(fruitTbl,Start,0):INDEX(fruitTbl,Count+Start-1,0),ROWS($1:1),COLUMNS($A:A)),"")
  • Then drag one column to the right
  • Select both cells and drag down at least until you start seeing blank cells
user_28729220
Related