Search-find-write

[smaill_19]

Hi, friends

i have a question. I need a formule. Is there anybody to help me.


At attachment when we enter value to f11 and h11 together, certificate number must be come up in cell of f13

[teylyn]

Hi smaill_19,

welcome to the forum.

can you give an example of the expected inputs for F11 and H11 and the expected result in F13 and explain the logic that leads to it?

Currently, as I see it, F11 is a label, no? Do you want to overwrite it?

[contaminated]

привет Казахстан
Можешь объяснить по подробнее чего именно тебе надо? Укажи плиз, где и какие ответы тебе нужны.

[teylyn]

smaill, contaminated, I'm happy to move this to the Non-English forum, if you want to continue the discussion in Russian, Azeri or Welsh, for that matter.

Please post only in English in the regular forums, though.

Contaminated, since this thread was started in English, please provide an English version of your post as well.

[smaill_19]

Thank you teylyn

sorry, correction

for example

will be entered to E11 value from green area
and
will be entered to G11 value from olumn of capacity

then the formule must show the certificate number in E13.

Cell of E13 will collate E11 and G11 simultaneously . Cell of E13 will be find out certificate number.

for example;

enter to E11 "H89"
enter to G11 "12"

E11 and G11 are exist row of fourth simultaneously. and " 86954 " will be come up in E13.

that's all.


beforehand
Thank you for help

[contaminated]

try in E13

Please Login or Register  to view this content.

This function shows desired output

[smaill_19]

than you contaminated,

The formule you write works.

Additionally

If E13 dosn't collate in green area and column of capacity, can we provide to have write "THERE ISN'T CERTIFICATE FOR THIS VALUE"

again

thank you for help

[teylyn]

contaminated, would you please translate your contribution in post #3

thanks

[contaminated]

Sure

Please Login or Register  to view this content.

As it is array formulae, Confirm with <Ctrl>+<Shift>+<Enter>

[contaminated]

Sure Teylyn

привет Казахстан (Hi Kazakhstan)

Можешь объяснить по подробнее чего именно тебе надо? Укажи плиз, где и какие ответы тебе нужны. (Can you pls explain detailly exactly what you want. Please specify where the output should be and and show your desired output)

[smaill_19]

=IF(OR(E11=D2:M7),SUMPRODUCT((E11=D2:M7)*(G11=B2:B7)*(A2:A7)),"THERE ISN'T CERTIFICATE FOR THIS VALUE")

contaminated

above formule does'nt work.

[contaminated]

Dear smaill 19
As it is array formulae, Confirm with <Ctrl>+<Shift>+<Enter>. İ have uploaded a dummy so u can see the of ühat İəm talking of.

As u can see formula wrapped into { } braces
You must not put them manually. They automatically appears as u confirmed your formulae with <Ctrl>+<Shift>+<Enter>, not just <Enter>

[JBeaucaire]

Try this, it's a regular formula, just a slight twist on Contaminated's formula:

=IF(SUMPRODUCT((E11=D2:M7)*(G11=B2:B7)*(A2:A7))=0, "NONE", SUMPRODUCT((E11=D2:M7)*(G11=B2:B7)*(A2:A7)))

[smaill_19]

contaminated and JBeaucaire

Thank you for help friends.

it works the formule contaminated and JBeaucaire has written.

[teylyn]

smaill,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save