Searching, matching then searching another list based on the match
 

Basically I was looking to do the following:

Example data:
1 ABC
96 SRE
32342 WRE

Let's A1 = 1, B1=96, and C1=32342. So I want to be able to lookup 1 on
another sheet, which the data may be as follows:

1 ABC .45 FJK .55 SDF .758
96 BDS 1.5 SSE 1.57 SRE 2.42
32432 CDW .66 WRE .891 WYQ 1.34

So basically it finds the "1" then it looks up A2=ABC and finds that and
then puts the number next to it...which in this case is .45
So the next row, it looks for "96", then looks for "SRE", and then gives me
2.42.

Any ideas? Thanks.

Searching, matching then searching another list based on the match
 

Try this:

This data on Sheet1 in the range A1:B3

1 ABC
96 SRE
32342 WRE

This data on Sheet2 in the range A1:G3

1 ABC .45 FJK .55 SDF .758
96 BDS 1.5 SSE 1.57 SRE 2.42
32432 CDW .66 WRE .891 WYQ 1.34

Assumes there are no duplicate letter codes on multiple rows. (otherwise, it
becomes much more complicated)

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER) on Sheet1 cell C1:

=INDEX(Sheet2!A$1:G$3,MATCH(A1,Sheet2!A$1:A$3,0),M AX((Sheet2!B$1:G$3=B1)*(COLUMN(Sheet2!B1:G3)-COLUMN(Sheet2!B1)+1)+2))

Copy down as needed.

Biff

"A.S." wrote in message
...
Basically I was looking to do the following:

Example data:
1 ABC
96 SRE
32342 WRE

Let's A1 = 1, B1=96, and C1=32342. So I want to be able to lookup 1 on
another sheet, which the data may be as follows:

1 ABC .45 FJK .55 SDF .758
96 BDS 1.5 SSE 1.57 SRE 2.42
32432 CDW .66 WRE .891 WYQ 1.34

So basically it finds the "1" then it looks up A2=ABC and finds that and
then puts the number next to it...which in this case is .45
So the next row, it looks for "96", then looks for "SRE", and then gives
me
2.42.

Any ideas? Thanks.