Am I being thick?

[Spellbound]

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

 

[JE McGimpsey]

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))

 

[Ron Coderre]

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,SUMPRODUCT(($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

 

[Gary''s Student]

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")

 

[Domenic]

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!

 

[Pete_UK]

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

 

[Spellbound]

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