Hi everyone

I am not an expert on Excel but have generally managed to get roun
most things but this problem has me stumped.

I have 3 rows of numbers; each row preceeded by a letter.

A 1 2 3 4 5 6 7 8 9 10
B 11 12 13 14 15 16 17 18 19 20
C 21 22 23 24 25 26 27 28 29 30

Basically if a cell contains one of these numbers, I want the formul
in an adjacent cell to fill in the letter from that row e.g. 6=A; 19=B
23=C.

I can make it work using nested IF statements but I felt that VLOOKU
might be a better solution but cannot make this work in any way shap
of form; even comparing it with other examples.

I can make it work with the 1st column of numbers i.e. 1, 11 or 21 bu
the minute I change the range to include the 1st Column (ABC) it come
up with #N/A error.

Would appreciate any help on resolving this problem ...thank

VLOOKUP uses the leftmost column as the lookup column, so you would
instead use the equivalent INDEX(MATCH(...)). One way:

=INDEX(A:A,MATCH(M1,B:B, TRUE))


In article <Spellbound.252kla_1143028202.89>,
Spellbound <Spellbound.252kla_1143028202.89>
wrote:
[..]

Try something like this:

With your data list in cells A1:K3
M1: (contains a number)

For no error checking:
N1: =INDEX($A$1:$A$3,SUMPRODUCT(($B$1:$K$3=M1)*ROW($1: $3)))

With error checking:
N1:
=IF(COUNTIF($B$1:$K$3,M1),INDEX($A$1:$A$3,SUMPRODU CT(($B$1:$K$3=M1)*ROW($1:$3))),"No match")

Note: In case window wrap occurs, there are NO spaces in either of those
formulas

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Pro


"Spellbound" wrote:
[..]

If you input is an integer, then you don't need to even reference the posted
table:


In A10 enter an integer between 1 and 30. Elsewhere enter:

=CHOOSE(ROUNDUP(A10/10,0),"A","B","C")

Assuming that A2:K4 contains your data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A2:A4,MATCH(TRUE,MMULT(--(B2:K4=M2),TRANSPOSE(COLUMN(B2:K4)^0))>0,
0))

....where M2 contains the number of interest. Note that if the number
occurs more than once, the formula will return the letter corresponding
to the first occurrence.

Hope this helps!

In article <Spellbound.252kla_1143028202.89>,
Spellbound <Spellbound.252kla_1143028202.89>
wrote:
[..]

I'll try yet again ... (2 posts not shown up yet)

Put your number in A1 (between 1 and 30), and this formula will give
you A, B or C as requested:

=CHAR(65+INT((A1-1)/10))

No need for a table.

Hope this helps.

Pete

Thanks to everyone for your solutions, it was surprising how man
different variants there were to the problem.

I liked the solution from Pete_UK the best but I suspect it will no
work if I had to make use of letters other than ABC, which do not ru
consecutively. Correct me if I am wrong.

I submitted the ABC x 3 x 10 more as a means of showing what I a
trying to achieve. The final table or data may use different letter
and there may be more than 10 numbers in each row.

After a quick experiment with all of them, I found the biggest proble
to be that most of them only return an error when the target cell i
empty although I suspect this can be resolved by modifying th
formula.

For the record, if there is no value in the target cell, then I nee
the cell with the formula to also remain blank.

So, I will be trying out all of your formulas over the next few days t
see which works best with my data.

Once again ...thanks to al