| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
two lookups?
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
|
|||
|
|||
|
two lookups?
I can offer two ways.
With you first lot of data on Sheet1 starting in A1, and the "table" on Sheet2 starting in A1 A) simple method with helper columns On Sheet1 in C1 use =MATCH(A1,Sheet2!$A$1:$A$3,0) In D1 use =MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$A$ 1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0) In 1 use =INDEX(Sheet2!$B$1:$G$3,C1,D1+1) B) without helper columns, In A1 use =INDEX(Sheet2!$B$1:$G$3,MATCH(A1,Sheet2!$A$1:$A$3, 0),MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$ A$1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0 )+1) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
two lookups?
Hello,
I tried the second way and end up with a #REF!. Any ideas why? Thanks. "Bernard Liengme" wrote: I can offer two ways. With you first lot of data on Sheet1 starting in A1, and the "table" on Sheet2 starting in A1 A) simple method with helper columns On Sheet1 in C1 use =MATCH(A1,Sheet2!$A$1:$A$3,0) In D1 use =MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$A$ 1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0) In 1 use =INDEX(Sheet2!$B$1:$G$3,C1,D1+1) B) without helper columns, In A1 use =INDEX(Sheet2!$B$1:$G$3,MATCH(A1,Sheet2!$A$1:$A$3, 0),MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$ A$1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0 )+1) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
two lookups?
I tried it the first way, and in the C1 I end up with a number (which doesn't
match anything?) then in the other 2 columns I end up with #N/A "Bernard Liengme" wrote: I can offer two ways. With you first lot of data on Sheet1 starting in A1, and the "table" on Sheet2 starting in A1 A) simple method with helper columns On Sheet1 in C1 use =MATCH(A1,Sheet2!$A$1:$A$3,0) In D1 use =MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$A$ 1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0) In 1 use =INDEX(Sheet2!$B$1:$G$3,C1,D1+1) B) without helper columns, In A1 use =INDEX(Sheet2!$B$1:$G$3,MATCH(A1,Sheet2!$A$1:$A$3, 0),MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$ A$1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0 )+1) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
two lookups?
I had a worksheet with these formulas working.
Send me you file (private email NOT the newsgroup) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "A.S." wrote in message ... I tried it the first way, and in the C1 I end up with a number (which doesn't match anything?) then in the other 2 columns I end up with #N/A "Bernard Liengme" wrote: I can offer two ways. With you first lot of data on Sheet1 starting in A1, and the "table" on Sheet2 starting in A1 A) simple method with helper columns On Sheet1 in C1 use =MATCH(A1,Sheet2!$A$1:$A$3,0) In D1 use =MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$A$ 1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0) In 1 use =INDEX(Sheet2!$B$1:$G$3,C1,D1+1) B) without helper columns, In A1 use =INDEX(Sheet2!$B$1:$G$3,MATCH(A1,Sheet2!$A$1:$A$3, 0),MATCH(B1,INDIRECT("Sheet2!$B"&MATCH(A1,Sheet2!$ A$1:$A$3,0)&":$G$"&MATCH(A1,Sheet2!$A$1:$A$3,0)),0 )+1) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "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 | |||
| Help with Crazy Lookups | Excel Worksheet Functions | |||
| Converging two lookups? (would save me AEONS of time!) | Excel Worksheet Functions | |||
| Q: Using lookups to normalize a database? | Excel Discussion (Misc queries) | |||
| External lookups won't load | Excel Discussion (Misc queries) | |||
| LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Linear Mode
