Index or lookup ??

This is a discussion on Index or lookup ?? within the Excel Questions forums, part of the Question Forums category; Hi, I have the numbers 1 thru 8 in cells A1:H1 In cells B2:H2I have the numbers 41578236 What I ...

1. Hi,

I have the numbers 1 thru 8 in cells A1:H1

In cells B2:H2I have the numbers 41578236

What I need is a formula that compares rows #1 and #2 and enters the results in row #3
For Example: if B2=1 then A3=2 / if F2=2 then B3=6 / if G2=3 then C3=7

Row #1 12345678
Row #2 41578236
Row #3 26713845

Bill

2. You need to explain your problem in some more detail.

If A1:A8 and B2:H21 have numbers, how can "if B2=1 then A3=2 / if F2=2 then B3=6 / if G2=3 then C3=7" be possible?

What role does A1:A8 play that is *different* than the contents of B2:H21?

How do you know to relate B2 and A3? F2 and B3? G2 and C3? What is the mapping strategy? How is B2=1 related to A3=2? And all the other transformation?

3. Hi

I will assume that B2:H2I should really be:
A2:H2

If so, try this.

1. Copy A1:H2 and Edit>Paste Special - Transpose to k1:L8

2. In M1 put = K1&L1 an copy down.

3. Copy paste special as values over the top of itself.

4. In N1:N8 place the corresponding values for the 2 number combination.

5. Now in A3 put

=VLOOKUP(A1&A2,\$M\$1:\$N\$8,2)

Copy acrross.

4. Hi,

Well I pretty much messed up yesterdays question (wife and kids rushing me out the door).I apologize for the bad post, please let me try again.

Cells A:1 thru H:1 have the numbers 1 thru 8 in them. These cells will always have the numbers in this exact order, this is my reference. Example: Cell A:1 contents 1 Cell B:1 contents 2 C:1 3 / D:1 4 / etc..

In the cells A:2 thru H:2 the numbers 1 thru 8 will be in these cells in any order they can change from day to day. In this example this will be there order 41578236 Example Cell A:2 contents 4 Cell B:2 contents 1 Etc...

In the third row cells A:3 thru H:3 you will have the one number results of comparing row #2 to row #1. Example: Cell A:2 has the number 4 in it so on the 4th cell over in row #3 you would put 1 because that is the value in Cell A:1 / Cell B:2 has the number 1 in it so in the 1st Cell A:3 you will 2 because that is the value in Cell B:1 Etc..

Below is a table trying to show what I am describing.

Row #1 Reference (always 1 thru 8 )
Row #2 (changes from day to day)
Row #3 (compares row#2 to row #1)

*** A B C D E F G H
R1 1 2 3 4 5 6 7 8
R2 4 1 5 7 8 2 3 6
R3 2 6 7 1 3 8 4 5

Bill

[ This Message was edited by: Billionzz on 2002-07-21 16:53 ]

[ This Message was edited by: Billionzz on 2002-07-21 16:55 ]

[ This Message was edited by: Billionzz on 2002-07-21 16:57 ]

[ This Message was edited by: Billionzz on 2002-07-21 16:58 ]

[ This Message was edited by: Billionzz on 2002-07-21 17:00 ]

5. Bill

enter

=MATCH(COLUMN(),\$A\$2:\$H\$2,0)

in a3 & copy accross

EDIT - note: this method works because you reference values in row 1 are the same as their column numbers. If the details you posted were just an eg & this isn't the case in your 'live' data, we will need a slightly different approach.

[ This Message was edited by: PaddyD on 2002-07-21 18:20 ]

Thank you very much for your help.

Bill

7. Hi,

I just now seen your edit note. The actual cells being used are as follows.

Example_______Actual
A:1 H:1_______AJ:2 AQ:2
A:2 H:2_______AJ:4 AQ:4
A:3 H:3________T:4 AA:4

Thanks
Bill

[ This Message was edited by: Billionzz on 2002-07-21 19:56 ]

8. in t4, enter:

=INDEX(\$AJ\$2:\$AQ\$2,1,OFFSET(\$AI\$4,-2,MATCH(COLUMN()-19,\$AJ\$4:\$AQ\$4,0),1,1))

and copy across

9. Hi Billionzz:

use =INDEX(\$AJ\$1:\$AQ\$1,MATCH(COLUMN(A:A),\$AJ\$2:\$AQ\$2,0))

see worksheet simulation
******** LANGUAGE="JavaScript" ************************************************************************>
 Microsoft Excel - y020721.xls ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 AJ3AK3AL3AM3AN3AO3AP3AQ3 =

AJ
AK
AL
AM
AN
AO
AP
AQ
AR
1
12345678*
2
41578236*
3
26713845*
4
*********
5
*********
 Sheet1 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Regards!

It worked great, thanks.

Bill

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•