Selecting Correct Data from file

Dear All,

Please see my file attached.
I have a file with below columns.
A = Product name
B = Stocklevel
C= Price
D= CAtegorie 1
E= Categorie 2
F = Categorie 3

Problem now is that the data from categorie 1,2 and 3 are mixed up
And I want to get out of these 3 colums all the data I need which is the data I have in the list data.
So basically a formula should look in column D, is there a match with a field in tab data, this should be shown. Is there no match, look in column E, no match, look in colum F.
Does something like this exist?

Thanks for your help.

I'm guessing you want to populate the File!H column with the information. Is that right?

I'm not sure how you want to populate that field. What I have so far is, if the name in col-D matches a name in Data!A:A then I put "Cat-1" in File col-H. If there is no match with col-D then I try col-E. If that results in a match then I put "Cat-2" in File col-H. Same process for col-F. If there's no match in any of the three columns then I put "Not found" in col-A. See the following picture..

simpauw.png

Formula in H2 and copied down:

Formula:

Please Login or Register  to view this content.

Is this on the right track?

By the way, why two identical columns on the Data worksheet?

Dear Geoff,

Thanks for your help! This indeed was close. Only instead of returning the Categorie number the return field should show the corresponding field. So if D2 matches with the list it should show D2.

Made the adjustmend and we are running!

=IF(NOT(ISNA(VLOOKUP(D2 Data!A:A; 1; FALSE)));D2;IF(NOT(ISNA(VLOOKUP(E2;Data!A:A;1;FALSE)));E2;IF(NOT(ISNA(VLOOKUP(F2;Data!A:A;1;FALSE)));F2;"Not found")))

Thanks for your help!

Glad to help and thanks for the feedback