Am I being thick?

Hi everyone

I am not an expert on Excel but have generally managed to get round 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 formula 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 VLOOKUP might be a better solution but cannot make this work in any way shape 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 but the minute I change the range to include the 1st Column (ABC) it comes up with #N/A error.

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

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@excelforum-nospam.com>,
Spellbound <Spellbound.252kla_1143028202.89@excelforum-nospam.com>
wrote:

> Hi everyone
>
> I am not an expert on Excel but have generally managed to get round
> 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 formula
> 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 VLOOKUP
> might be a better solution but cannot make this work in any way shape
> 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 but
> the minute I change the range to include the 1st Column (ABC) it comes
> up with #N/A error.
>
> Would appreciate any help on resolving this problem ...thanks

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@excelforum-nospam.com>,
Spellbound <Spellbound.252kla_1143028202.89@excelforum-nospam.com>
wrote:

> Hi everyone
>
> I am not an expert on Excel but have generally managed to get round
> 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 formula
> 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 VLOOKUP
> might be a better solution but cannot make this work in any way shape
> 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 but
> the minute I change the range to include the 1st Column (ABC) it comes
> up with #N/A error.
>
> Would appreciate any help on resolving this problem ...thanks

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


"Spellbound" wrote:

>
> Hi everyone
>
> I am not an expert on Excel but have generally managed to get round
> 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 formula
> 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 VLOOKUP
> might be a better solution but cannot make this work in any way shape
> 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 but
> the minute I change the range to include the 1st Column (ABC) it comes
> up with #N/A error.
>
> Would appreciate any help on resolving this problem ...thanks
>
>
> --
> Spellbound
> ------------------------------------------------------------------------
> Spellbound's Profile: http://www.excelforum.com/member.php...o&userid=32697
> View this thread: http://www.excelforum.com/showthread...hreadid=525206
>
>

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")
--
Gary''s Student


"Spellbound" wrote:

>
> Hi everyone
>
> I am not an expert on Excel but have generally managed to get round
> 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 formula
> 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 VLOOKUP
> might be a better solution but cannot make this work in any way shape
> 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 but
> the minute I change the range to include the 1st Column (ABC) it comes
> up with #N/A error.
>
> Would appreciate any help on resolving this problem ...thanks
>
>
> --
> Spellbound
> ------------------------------------------------------------------------
> Spellbound's Profile: http://www.excelforum.com/member.php...o&userid=32697
> View this thread: http://www.excelforum.com/showthread...hreadid=525206
>
>

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@excelforum-nospam.com>,
Spellbound <Spellbound.252kla_1143028202.89@excelforum-nospam.com>
wrote:

> Hi everyone
>
> I am not an expert on Excel but have generally managed to get round
> 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 formula
> 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 VLOOKUP
> might be a better solution but cannot make this work in any way shape
> 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 but
> the minute I change the range to include the 1st Column (ABC) it comes
> up with #N/A error.
>
> Would appreciate any help on resolving this problem ...thanks

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 many different variants there were to the problem.

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

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

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

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

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

Once again ...thanks to all