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

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
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,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:


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


--
Spellbound
------------------------------------------------------------------------
Spellbound's Profile: http://www.excelforum.com/member.php...o&userid=32697
View this thread: http://www.excelforum.com/showthread...hreadid=525206