CODEWITHSUNDEEP
excelexcel-formula
TiesA
TiesA

Reputation: 15

Returning a row number based on two (or more) criteria

To explain what I'm trying to do: I have a sheet named 'info sheet' in this example that contains several columns of information like so:

A (ID numbers) B (positions) Column C and above
165 consultant misc
165 technician misc
312 technician misc
312 technician misc
etc technician misc

In a second sheet I want to create a report layout, where you can either select or enter an ID number, and have only the rows with the matching ID numbers show up. For showing the row entries with ID number 165, I have the following formulas for that (the info sheet has approx. ~250 rows):

MATCH(165;OFFSET('info sheet'!A1;0;0;250;1);0)

This formula returns the first matching row number. For the below second formula, assume this first formula to be within cell B1 on my report sheet

MATCH(165;OFFSET('info sheet'!A1;B1;0;250;1);0)+B1

This formula returns the second match by adding the result of the first formula and offsetting it (and so forth for potential further matches)

This works perfectly, but I want to add an extra criteria to both formulas. Right now I'm only matching the row based on column A / the ID number, but I also want to match based on the position value in column B and I'm struggling to find a solution.

For example, I want to return the row number where column A is 165 and column b is technician

I've been able to create a formula that returns the first match:

MATCH(165;IF('info sheet'!b1:b250="technician";OFFSET('info sheet'!A1;B1;0;250;1);0)

But the previous method of adding the offset of the first match doesn't work in the subsequent formulas, and I have no idea why. Any help (or suggestions for alternative methods) would be appreciated!

Thank you for reading and regards

Upvotes: 0

Views: 2459

Answers (2)

MGonet
MGonet

Reputation: 2027

When applying your method, you must also apply the offset to the condition you are adding.
But if you have a relatively new Excel and the FILTER function in it, you can apply more intuitive formulas.
See the screenshot.
Sequence
Formulas as text:
D2 : =MATCH(165,OFFSET($A$2,D1,0,20,1),0)+D1
E2 : =MATCH(165,IF(OFFSET($B$2,E1,0,20,1)="a",OFFSET($A$2,E1,0,20,1)),0)+E1
J2 : =FILTER(SEQUENCE(ROWS($A$2:$A$21)),($A$2:$A$21=165)*($B$2:$B$21="a"))
The other formulas are similar, for parameter =312.

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8375

So, first quick go:

MATCH(F3&E3,D3:D7,0)

enter image description here

Used the ID concatenated with the position, also this gives the row number in the table, row() can be used to get the sheet row number.

Upvotes: 0

Related Questions