Excel Index / vlookup between varied row amounts-CodePudding

I am trying to use a vlookup / index to pull some numbers. Sample data attached:

A: merged cells (the number is stored in the top of cell of the merge)

B: Only numbers I am looking for from this one are the totals in blue

E: Number to lookup

F: Totals (expected numbers shown in green)

I have done something similar in the past just cant see how I got it to work.

Note: The amount of rows for each number in column A could vary the smallest they will appear is as 2 rows e.g. rows 4 / 5 but could go up infinitely.

If anyone knows how I could get the expected result that would be great!

CodePudding user response：

The formula below would work

=IFNA(INDEX($B$1:$B$14,MATCH(D2,$A$1:$A$14,0)-1),INDEX($B$1:$B$14,COUNT($B$1:$B$14)))

if the values in column D were always in the same order as those in column A:

CodePudding user response：

Or,

try this formula solution of which criterias in column D were NOT in the same order as in Column A.

In E1, formula copied down :

=INDEX(B:B,MATCH(D1,A:A,0) MATCH(1,FREQUENCY(1,N(INDEX(A:A,MATCH(D1,A:A,0) 1):A$14<>"")),0)-1)

CodePudding user response：

You may try this formula as well, works with any order using INDEX(), AGGREGATE(), LOOKUP() & ROW() Functions,

• Formula used in cell I1

=INDEX($B$1:$B$14,
AGGREGATE(14,6,ROW($B$1:$B$14)/
(H1=LOOKUP(ROW($A$1:$A$14),ROW($A$1:$A$14)/
($A$1:$A$14<>""),$A$1:$A$14)),1))

Let me explain why this works,

• LOOKUP() Function creates an array of the Column A values

=LOOKUP(ROW($A$1:$A$14),ROW($A$1:$A$14)/($A$1:$A$14<>""),$A$1:$A$14)

Refer Column L

• Select the above formula and press F9 Function Key, you will find it creates an array of 12345, 43643, 23245, 98989 for the number of times it has a value in Column B

{12345;12345;12345;43643;43643;23245;23245;23245;23245;23245;98989;98989;98989;98989}

• Next I did a Boolean Check i.e. whether it matches with my criteria or not, and returns an array of TRUE's & FALSE's respectively

={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}

• To return the relative position of my criteria in an array I simply did the one as shown below, which gives me the respective row numbers,

Refer Column L

=ROW($A$1:$A$14)/(H1=LOOKUP(ROW($A$1:$A$14),ROW($A$1:$A$14)/($A$1:$A$14<>""),$A$1:$A$14))

• As you can see on doing above it returns along with some errors as well, hence we wrapped the whole within an AGGREGATE() function

--> Where 14 refers to function_num that is LARGE(),

--> Where 6 refers to options that is Ignore Error Values,

--> Where array is the

ROW($A$1:$A$14)/(H1=LOOKUP(ROW($A$1:$A$14),ROW($A$1:$A$14)/($A$1:$A$14<>""),$A$1:$A$14))

--> and lastly [k] we need largest of all in the array, which shall return me the row number, therefore, in the above it will return 10 for 98989 as in cell H1 and if you see E1 it will return 3 12345 and so on so forth for others as well.

Therefore when we have got the row number or the position we wrapped the whole within an INDEX() Function to get our desired output respectively!

• Formula used in cell F1

=INDEX($B$1:$B$14,
AGGREGATE(14,6,ROW($B$1:$B$14)/
(E1=LOOKUP(ROW($A$1:$A$14),ROW($A$1:$A$14)/
($A$1:$A$14<>""),$A$1:$A$14)),1))