Lookup? or What??
 

I have this table in a spreadsheet.

a b c d e f g
1 1 2 3 4 5 6
2 A Z U P K F A
3 B V Q L G B
4 C W R M H C
5 D X S N I D
6 E Y T O J E

Below is what i need have displayed.

a b c d
7 J U M P
8 E5 A2 C4 A3

So i need to use the first grip to make up the answers in second in row 8.

I can use the concatenate to get the Letter and number together, however i
dont know how to get the letters and numbers. I used the lookup however it
only worked for the first row and the rest of the rows dont work? help on
this would be great!!

Lookup? or What??
 

Hi Kim,

Either I don't understand your question or you made a slight mistake; I would have thought row 8 would have to be

E4 A2 C3 A3


If that is correct, then here is a solution which gives the answer in row 13. You can hide the intermediate rows or change the
formulas and put them somewhere else.

In A8:

=IF(ISNA(MATCH(A$7,$B2:$G2,0)),"",$A2&MATCH(A$7,$B 2:$G2,0))

Copy down to a total of five rows and copy those to the right, to get 6 columns.

In A13, enter:

=A8&A9&A10&A11&A12

Copy to the right to get 6 columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"KimC" wrote in message ...
|I have this table in a spreadsheet.
|
| a b c d e f g
| 1 1 2 3 4 5 6
| 2 A Z U P K F A
| 3 B V Q L G B
| 4 C W R M H C
| 5 D X S N I D
| 6 E Y T O J E
|
| Below is what i need have displayed.
|
| a b c d
| 7 J U M P
| 8 E5 A2 C4 A3
|
| So i need to use the first grip to make up the answers in second in row 8.
|
| I can use the concatenate to get the Letter and number together, however i
| dont know how to get the letters and numbers. I used the lookup however it
| only worked for the first row and the rest of the rows dont work? help on
| this would be great!!

Lookup? or What??
 

I dont think i explained it right. I am trying to make a code with something
similar to coordinates - so for the letter G the code would be B5, in the
first column i have listed what i am naming the rows and the first row is
what i am naming the columns. the diagram might not make sense due to having
my user listed version and the excel listed as well

"Niek Otten" wrote:

Hi Kim,

Either I don't understand your question or you made a slight mistake; I would have thought row 8 would have to be

E4 A2 C3 A3


If that is correct, then here is a solution which gives the answer in row 13. You can hide the intermediate rows or change the
formulas and put them somewhere else.

In A8:

=IF(ISNA(MATCH(A$7,$B2:$G2,0)),"",$A2&MATCH(A$7,$B 2:$G2,0))

Copy down to a total of five rows and copy those to the right, to get 6 columns.

In A13, enter:

=A8&A9&A10&A11&A12

Copy to the right to get 6 columns

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"KimC" wrote in message ...
|I have this table in a spreadsheet.
|
| a b c d e f g
| 1 1 2 3 4 5 6
| 2 A Z U P K F A
| 3 B V Q L G B
| 4 C W R M H C
| 5 D X S N I D
| 6 E Y T O J E
|
| Below is what i need have displayed.
|
| a b c d
| 7 J U M P
| 8 E5 A2 C4 A3
|
| So i need to use the first grip to make up the answers in second in row 8.
|
| I can use the concatenate to get the Letter and number together, however i
| dont know how to get the letters and numbers. I used the lookup however it
| only worked for the first row and the rest of the rows dont work? help on
| this would be great!!