I'm not sure the best way to do this. I have 2 workbooks (I can easily
combine them in one worksheet-that is not the problem). In the first I
have a column of identifiers and corresponding data in the adjacent
column. In a second workbook I have a list of identifiers, which
includes all the identifiers present in the first workbook, and a
column of data associated with them. After I combine them into a
single worksheet I want to look up the data value associated with the
same identifier and move it next to the first set of data. For example:

1A 1M Convert to 1 A M
3B 2N 3 B O
5C 3O 5 C Q
4P
5Q

The real files are much larger and the identifiers are not listed in
order. In Walkenbach's Excel 2003 Bible Chapter 12 page 256, it
suggests using Index and Match functions as such:

=INDEX(F2:F21,MATCH(B1,D2:D21,0))

But this would only return the corelate for the identifier in B1. Can
one somehow drag down this formula in a column B so that all B1:B2000
is matched or can one replace B1 with an array? or is there some other
way to do this?
Thanks,
Dennis

Hi!

Assume:

A............B............C..........D..........E
1 1A.........1M...........1...........A..........M
2 3B.........2N............3...........B..........O
3 5C.........3O............5...........C..........Q
4 4P
5 5Q

Formula C1: =LEFT(A1,1)

Formula D1: =RIGHT(A1,1)

Formula E1 (entered with the key combo of CTRL,SHIFT,ENTER):

=INDEX(RIGHT(B$1:B$5,1),MATCH(C1,LEFT(B$1:B$5,1),0 ))

Select the range C1, D1, E1 and drag copy down as needed.

Something tells me it won't be that easy! <g

Biff

wrote in message
oups.com...
I'm not sure the best way to do this. I have 2 workbooks (I can easily
combine them in one worksheet-that is not the problem). In the first I
have a column of identifiers and corresponding data in the adjacent
column. In a second workbook I have a list of identifiers, which
includes all the identifiers present in the first workbook, and a
column of data associated with them. After I combine them into a
single worksheet I want to look up the data value associated with the
same identifier and move it next to the first set of data. For example:

1A 1M Convert to 1 A M
3B 2N 3 B O
5C 3O 5 C Q
4P
5Q

The real files are much larger and the identifiers are not listed in
order. In Walkenbach's Excel 2003 Bible Chapter 12 page 256, it
suggests using Index and Match functions as such:

=INDEX(F2:F21,MATCH(B1,D2:D21,0))

But this would only return the corelate for the identifier in B1. Can
one somehow drag down this formula in a column B so that all B1:B2000
is matched or can one replace B1 with an array? or is there some other
way to do this?
Thanks,
Dennis

Another way to play with ..
(Link to a sample file is provided below)

In Sheet2, assume the source data is in A2 down, viz:

1A
1M
3B
2N
5C
3O
4P
5Q
4X
2W
etc

List the 5 identifiers across in say G1:K1, i.e.: 1,2,3,4,5

Put in B2: =IF($A2="","",IF(LEFT($A2,1)+0=G$1,ROW(),""))

Copy B2 across 5 cols to F2 (as we have 5 identifiers in G1:K1), fill down
to say, F30 to cover the expected data range. Note that B1:F1 should be left
empty, that's why we've had to "offset" the 5 identifiers list to be say,
just adjacent to cols B to F.

In a new Sheet3
----------------
List the identifiers across in B1:F1, i.e.: 1,2,3,4,5

Put in B2:

=IF(ISERROR(SMALL(Sheet2!B:B,ROWS($A$1:A1))),"",MI D(INDEX(Sheet2!$A:$A,MATCH
(SMALL(Sheet2!B:B,ROWS($A$1:A1)),Sheet2!B:B,0)),SE ARCH(B$1,INDEX(Sheet2!$A:$
A,MATCH(SMALL(Sheet2!B:B,ROWS($A$1:A1)),Sheet2!B:B ,0)))+1,99))

Copy B2 across to F2, fill down to say, F30
(cover the same range as in Sheet2)

Sheet3 will return the listing below.
It's what you want, except that it's transposed.
(as it is, this list in Sheet3 may be also be acceptable?)

1 2 3 4 5
A N B P C
M W O X Q

In Sheet1
-----------
We'll "re-transpose" what's in Sheet3 to get the final desired listing

Put in A1:

=IF(OFFSET(Sheet3!$B$1,COLUMNS($A$1:B1)-1,ROWS($A$1:B1)-1)=0,"",OFFSET(Sheet
3!$B$1,COLUMNS($A$1:B1)-1,ROWS($A$1:B1)-1))

Copy A1 across to AD1
(cover the same range as in Sheet3, except "column-wise")
Fill down to AD5

Sheet1 will return the required listing:

1 A M
2 N W
3 B O
4 P X
5 C Q

Here's a sample file with the implemented construct:
http://flypicture.com/p.cfm?id=50775

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: schuldzd_wksht_2.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
wrote in message
oups.com...
I'm not sure the best way to do this. I have 2 workbooks (I can easily
combine them in one worksheet-that is not the problem). In the first I
have a column of identifiers and corresponding data in the adjacent
column. In a second workbook I have a list of identifiers, which
includes all the identifiers present in the first workbook, and a
column of data associated with them. After I combine them into a
single worksheet I want to look up the data value associated with the
same identifier and move it next to the first set of data. For example:

1A 1M Convert to 1 A M
3B 2N 3 B O
5C 3O 5 C Q
4P
5Q

The real files are much larger and the identifiers are not listed in
order. In Walkenbach's Excel 2003 Bible Chapter 12 page 256, it
suggests using Index and Match functions as such:

=INDEX(F2:F21,MATCH(B1,D2:D21,0))

But this would only return the corelate for the identifier in B1. Can
one somehow drag down this formula in a column B so that all B1:B2000
is matched or can one replace B1 with an array? or is there some other
way to do this?
Thanks,
Dennis