Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Could someone please help me w/ matching the contents of each two sheets? | Excel Discussion (Misc queries) | |||
Copy # fm one list to matching id in list 2? | Excel Worksheet Functions | |||
Formula for Searching & matching two values in excel | Excel Discussion (Misc queries) | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
limit cell list selection based on the selection of another list | Excel Worksheet Functions |