Search a value across a range of two columns and return the first 10 results

[Abdmujib]

Hello, I have been trying find the value on DC4 that is (valenranga) from range of two columns i.e CS or CU for the first 10. It can either find it in either of the two colum the it should return corresponding result of CS, CT and CU.

E.g the first and second results would be

Odd *2 - 1* Valenranga
Valenranga *0 - 4* Molde

For the three corresponding column respectively.


Pls Kindly don't mind the interface of google sheet i snapped

 

[Abdmujib]

Can you help @Anthony47

 

[Anthony47]

Maybe:
-select DC4:DE4
-insert in the formula bar the formula =IFERROR(INDEX($CS$1:$CU$1000,SMALL(IF(($CS$5:$CS$1000=$DC$4)+($CU$5:$CU$1000=$DC$4),ROW($CS$5:$CS$1000),""),ROW(A1)),0),"")
-confirm the formula using the combination Contr-Shift-Enter
-copy DC4:DE4 down for as many as results you want

Try...

 

[Abdmujib]

Maybe:
-select DC4:DE4
-insert in the formula bar the formula =IFERROR(INDEX($CS$1:$CU$1000,SMALL(IF(($CS$5:$CS$1000=$DC$4)+($CU$5:$CU$1000=$DC$4),ROW($CS$5:$CS$1000),""),ROW(A1)),0),"")
-confirm the formula using the combination Contr-Shift-Enter
-copy DC4:DE4 down for as many as results you want

Try...

Thank you @Anthony47. It works

 

[Anthony47]

Thank you for the feedback

 

[Abdmujib]

Hello @Anthony47, what I want it to find it only in the right column (CS), what formula would I use. I removed the +$C$U...... In the "if" argument but didn't work

 

[Anthony47]

Try using the same procedure but this formula:

=IFERROR(INDEX($CS$1:$CU$1000,SMALL(IF($CS$7:$CS$1000=$DC$6,ROW($CS$7:$CS$1000),""),ROW(A1)),{1,2,3}),"")

 

[Abdmujib]

Try using the same procedure but this formula:

=IFERROR(INDEX($CS$1:$CU$1000,SMALL(IF($CS$7:$CS$1000=$DC$6,ROW($CS$7:$CS$1000),""),ROW(A1)),{1,2,3}),"")

Thank you @Anthony47, it didn't work effectively. first of all, it wasn't like the previous one that populate for all the column in that row.
secondly, it brought out a value which does not satisfy the condition in the "if" function.

 

[Anthony47]

1) Can you confirm you used "the same procedure":
-select DC4:DE4
-insert in the formula bar the updated formula
-confirm the formula using the combination Contr-Shift-Enter
-copy DC4:DE4 down for as many as results you want

2)Depending on your language, you might need to use a different "field separator" in the array constant: try using "\" i.e. {1\2\3} instead of {1,2,3}

3) Can you show how look your data and which output the formula generates? An image should be sufficient

 

[Anthony47]

If the value to search is in DC4, your list starts on CS5, and the output need to be created from DC5:DE5 then the formula should be entered using the CSE procedure in DC5:DE5 and should be

=IFERROR(INDEX($CS$1:$CU$998,SMALL(IF($CS$5:$CS$998=$DC$4,ROW($CS$5:$CS$998),""),ROW(A1)),{1,2,3}),"")

(with the doubt about the field separator within the braces {})