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.
|