Lookup? or What??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KimC - 27 Apr 2008 11:38 GMT

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

Reply to this Message

Niek Otten - 27 Apr 2008 12:28 GMT

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have this table in a spreadsheet.
|
[quoted text clipped - 18 lines]
| only worked for the first row and the rest of the rows dont work? help on
| this would be great!!

Reply to this Message

KimC - 27 Apr 2008 12:44 GMT

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

> Hi Kim,
>
[quoted text clipped - 39 lines]
> | only worked for the first row and the rest of the rows dont work? help on
> | this would be great!!

Reply to this Message