Ok Pete!
I will take note of it! Actually I tried with Ragdyer's formula (i messed
up a bit, but finally got it) and understood your point. Thanks and thanks
Ragdyer too.
--
KBZ


"Pete_UK" wrote:

As you use Excel more, you will discover some of the drawbacks with
the formula that Mike gave you. Suppose you enter a value in column A
that is not in the lookup table, like "A" or 123? The formula returns
the error #N/A which means that the value can't be found, but you
might want to show something else instead of the error. Another
situation is that you might have copied the formula down a number of
rows in anticipation of entering data later, but again a blank doesn't
exist in the lookup table so you will get an error.

Ragdyer's formula takes account of these possible errors (and more),
and returns the message "No match" instead of the rather unhelpful #N/
A. I suggest you study it for future use.

Pete

On Jul 5, 11:50 pm, Karoline
wrote:
Mmmm interesting approach and step by step explanation. Thanks Ragdyer..

It seems to be that there are multiple ways to reach the result, though I
must confessed using the VLOOKUP strategy suggested by Mike H works better
for amateurs like me, since it reduce the probability to make a mistake while
inserting the data.

How ever thank you very much for taking your time and sharing your knowledge!
--
KBZ



"Ragdyer" wrote:
Now, you mentioned being able to enter *either* a group *or* an interest,
and have the associated value for either one displayed.

The first step is to create a datalist that will create the associations
between the groups and the interests.

Locate this in an out-of-the-way area, say Y1 to Z10.
For this example, let's use in Y1 to Y10 regular numbers, 1 to 10.
In Z1 to Z10 list the various interests.

Say you enter a value from either category in A1, and you wish the
affiliated data to display in B1.

So, use this formula in B1:

=IF(A1="","",IF(ISNA(MATCH(A1,Y1:Y10,0)),IF(ISNA(M ATCH(A1,Z1:Z10,0)),"NO
Match",
INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MAT CH(A1,Y1:Y10,0))))

This should poll your datalist in *either* direction.
--
HTH,

RD