[Solved] Match data and fetch common in third cell

[yuv.370]

I have two row A and B both have some common number, if i type 80900 in D2 it should give me all number from b that matches from A

[Hagar Delest]

Hi and welcome to the forum!

Please read the Survival Guide for the forum.
I have changed all the caps in your message. Caps are for shouting and it is a quiet place here.
If you have vision difficulties, increase the font size of the whole page because nobody will reply in all caps anyway.

[Lupp]

The version you give in your signature is a very old AOO, but the example file was created with LibO 7.4.4.2. Strange!
Anyway, you surely mean the columns A and B. Both contain numbers, and the presentation of numbers is affected by formatting. Numbers used for IDs and the like should ne entered as text.
As I understand the question, you expect all the result from B where A matches what you put into D to be returned as a result in a single cell. This can only be done converting the numbers to strings and using the function TEXTJOIN() in array-mode. (Otherwise you would need to resort to user code.)
TEXTJOIN() is only available in LibO V 4.2 or higher.
If you actually run such a version you may study the attached example.

aoo112066anotherMatchRe.ods

(15.52 KiB) Downloaded 59 times

[Alex1]

I tried 80400 in an empty row and got "<none".
Use this in E2 instead, entered as an array formula (i.e. using Ctrl+Shift+Enter) and use the fill handle:
=TEXTJOIN(";";1;IF(A$2:A$11=D2;B$2:B$11;"")) & IF(CURRENT()="";"<none>";"")

[Lupp]

Thanks for the correction @Alex1!

[yuv.370]

Hi still getting error in formula

[Lupp]

1. Attach your example sheet.
2. Next to nobody else here will have an OO.o V3.1 for checks.

[yuv.370]

My OO version is 4.1.11 example attached

[Lupp]

aoo112066anotherMatchRe_Filtering.ods

(22.25 KiB) Downloaded 49 times

If you consider to shift to LibreOffice, and to use a fresh version (24.8 or higher) the filtering solution in the attached sheet will work.
To do such filtering with formulas of the "old tradition" is too complicated, and requires helper ranges.

The solution using TEXTJOIN() will only work in LibO V 4.2 or higher.
Old OO.o has no means supporting the task without resorting to user code (macros).

[Alex1]

My OO version is 4.1.11 example attached

This version contains no formulas at all, so how do we know what the error is?

[Lupp]

See also:

aoo112066anotherMatchRe_FilteringWithHelpers.ods

(26.06 KiB) Downloaded 69 times