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.

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.

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.

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.

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.