Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup or Match ?
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. |
#2
|
|||
|
|||
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 "carl" wrote: 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. |
#3
|
|||
|
|||
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "carl" wrote in message ... 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. |
#4
|
|||
|
|||
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup & match | Excel Discussion (Misc queries) | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions |