Lookup or Match ?

[Guest]

My data comes in like this:

ColA ColB ColC ColD ColD
Line1 !55 200 !202 ABC
Line2 !202 EFG !55 1000

I am trying to fill in a table like this:

!55 !202
Line1 200 ABC
Line2 1000 EFG

So, in this new table, I would like the formula to look at the ColA (eg
"Line1") and ColB (eg "!55") and return the appropriate value from the
original data table.

Is this possible ?

Thank you in advance.

 

[Guest]

Hi,

Suppose your ColA cell is A1 and your two input cells (with 'Line2' and
'!202' in for example) are in A6 and A7.

Then B6 should be

=MATCH(A6,A2:A3,FALSE)

and B7 should be

=MATCH(A7,OFFSET(B1,B6,0,1,4),FALSE)

and the result cell should be

=OFFSET(B1,B6,B7,1,1)

Obviously change the size of the arrays or the length of the range in the
OFFSET function (the 4) as needed.

HTH,

David

 

[Max]

Another play ..

Assume the source below is in Sheet1, data from row2 down

ColA ColB ColC ColD ColD
Line1 !55 200 !202 ABC
Line2 !202 EFG !55 1000

In Sheet2
--------
Assume this table below is in A1:C3

!55 !202
Line1 ?? ??
Line2 ?? ??

Put in B2:

=OFFSET(Sheet2!$B$1,MATCH($A2,Sheet2!$A:$A,0)-1,MATCH(B$1,Sheet2!$B2:$E2,0))

then copy B2 across to C2, fill down to C3 to populate the grid

This will return the desired results:

!55 !202
Line1 200 ABC
Line2 1000 EFG

 

[Max]

Typos:

Assume the source below is in Sheet1

Sheet1 should read as Sheet2

In Sheet2

should be: In Sheet3

It's also assumed that col A's Line1, Line2 are in identical order in both
sheets

 

[Max]

It's also assumed that col A's Line1, Line2 are in identical order in both

sheets

If the above is not necessarily true, then try instead in Sheet3:

Put in B2:

=INDEX(OFFSET(Sheet2!$B$1:$E$1,MATCH($A2,Sheet2!$A:$A,0)-1,),MATCH(B$1,OFFSE
T(Sheet2!$B$1:$E$1,MATCH($A2,Sheet2!$A:$A,0)-1,),0)+1)

Copy across and fill down as before