Finding values

[georgesme]

Hello everyone,

I have an excel document :

from column 1 to 27 there are values

column 28 is a list of names of persons

every line is related to a person , that own the values indicated on his line


i want a function that can look up a value in this board and tell me the person who owns it

i am french ,so i tried RECHERCHEV(VALUE;BOARD;28;0) , it didnt work , it just searched in the first column.....

So i would like to have that search feature if possible , tahnk you


if i wasnt clear , plz tell me i will restate my problem

thank you

[NBVC]

Bonjour,

Try

=INDEX(Board,SUMPRODUCT(--ISNUMBER(SEARCH("#"&value&"#","#"&Board&"#"))*ROW(Board)),28)

In French, perhaps:

=INDEX(Board;SOMMEPROD(--ESTNUM(CHERCHE("#"&value&"#";"#"&Board&"#"))*LIGNE(Board)),28)






value is the value to search.


Edit: Adjusted formula to satisfy pre-defined names

[georgesme]

i will try this at once , but the thing is taht i am not working with numbers , im working with letters , such as : AB , BC , DC.....or ABC .... hope it will work tho .... if not , got an anothere propostition ?


thank you =)

[NBVC]

It will work with letters too. The Isnumber(search()) searches for position of a string and if it finds a position (which is numerical) it returns TRUE.

Note that I changed the formulas to suit your pre-defined Value & Board.

[georgesme]

I have a problem putting it in the document , its kinda not working , so i attached my document , hope u can try it or apply it for me :

The result to be searched is : "E31" of Sheet Créateur
The matrix where it will be searched is "A6:AB300" of Sheet Fonctions
The "AB6" column contains the names that should be displayed if the result is on theire row

This is my document link :

http://www.geocities.com/georgesmoubarak/cra.xls

[NBVC]

I am a little confused.

You have many duplicate entries in your search table (I see a few AB entries).... How do you know which line to search in?

Also, which cell do you want the result in?

[georgesme]

I want him to search the cell "E31" in sheet createur , and match it in the board in fonctions from A6:AB300 , if there are many results , i dont have a problem as long as he picks one of the results , randomly , doesnt really matter.

and once he finds for exp AB in C55 , he should display in C33 sheet createur the name in "AB55"


than you =)


hope i was clear enough...

[NBVC]

The fact that you have duplicates is what makes it a little tricky. I will work on it and see if I can figure it out for you...

btw, there is no info in C55 or AB55 of Fonctions!....

[georgesme]

It was just an example ..... this board or matrix will be filled later on , its kinda of a database that will be filled.

so i am trying to make it work for later results , take ur time working on it , and reply anytime u got something for me , i will tell u in case i got a solution

really thank you , i aprreciate it =)

[NBVC]

Okay, I hope I've got it.

See attached...

Formula used in C33 of Créateur:

Please Login or Register  to view this content.

noticed that I extended range to AB301.... I place an "End of File" marker in Fonctions!AB301.... this is so that when the input values are not found in the table, you get a message that you reached End Of File with no matches.

I hope this helps. Please let me know.

[georgesme]

Sorry for the late reply , i was kinda on weekend , the formula is working , but i wasnt able to move it to my document , so im updating the one i sent you =)

THank you very much , that was very helpful

Georges