Regex with Index/Match

[SilvioSiefke]

Hello,

i search a way to extract a number (example (2984)) and use this number in multiple statement.

The table, need to extract 2992 from D (example…) so that we can search (D) DN 25 with main 2992.

| A      |B               |C       |D       |E       |F      |
| Values | 		          |        |example (2992)   |       |
|--------|----------------|--------|--------|--------|-------|
| Sub    | Main           | Value  | Search | Result | Right |
| 2996   | 2992           | DN 6   | DN 25  | 3007   | empty |
| 2997   | 2992           | DN 8   | DN 32  | 3007   | 3002  |
| 2998   | 2992           | DN 10  | DN 40  | 3007   | empty |
| 2999   | 2992           | DN 15  |        |        |       |
| 3000   | 1415           | DN 20  |        |        |       |
| 3001   | 2000           | DN 25  |        |        |       |
| 3002   | 2992           | DN 32  |        |        |       |
| 3003   | 1500           | DN 40  |        |        |       |
| 3004   | 2992           | DN 50  |        |        |       |
| 3005   | 2992           | DN 65  |        |        |       |
| 3006   | 2992           | DN 80  |        |        |       |
| 3007   | 2992           | DN 100 |        |        |       |

The formular I try:

=INDEX(A$2:A$14;MATCH(1;(B$2:B$14=IFERROR(REGEX($D$1;"[^[:digit:]]";"";"g");"0"))*(C$2:C$14=D3));0)

But this work not, you can see in result ever bring 3007 but right would be empty, 3002 and empty.

Maybe someone had Idea can combine it.

Thank you & Nice Day
demo.ods (21.4 KB)

Silvio

[Villeroy]

I don’t understand what you are after but REGEX returns text. You compare text with numbers. Try VALUE(REGEX(…))

[PKG]

How about

=SUMIFS($A$3:$A$14;$B$3:$B$14;REGEX($D$1;"[^[:digit:]]";"";"g");$C$3:$C$14;D3)

demo.ods (13,1 KB)

[erAck: edited to codify using ``` (as usual wrong " quote characters were displayed)]

[SilvioSiefke]

Thanks good Idea I so fixed in index/match other I had not in mind.

Nice day and Thank you
Silvio