Index & Match from Other Sheets?
 

Hi,

This is a followup question to my previous one which was answered by Stefi
(thanks, Stefi!) on using the INDEX and MATCH command. I'd like to know it
if's possible to use the command to compare a calculated number (C13) against
data located on different sheets and how could that be done?

So, for example, if C13 (on Sheet 1) is calculated as 12.22, this number
would be checked against the data on Sheets 2, 3, and 4. If C13 doesn't
match any of the listed items, the idea is to settle for the closest but
higher number in A1 and return the corresponding info in B1 from all 3 sheets
to Sheet 1. So it would look something like this:

SHEET #1
12.22
d2
c12
j4

SHEET #2
A1 B1
10.83 d1
13.82 d2
16.28 d3
18.47 d4

SHEET #3
A1 B1
21.03 c12
22.36 c13
23.69 c14
25.00 c15

SHEET #4
A1 B1
6.64 j1
9.21 j2
11.35 j3
13.28 j4

Thanks for any help or ideas on this.

Les

Index & Match from Other Sheets?
 

you can use the same formulas to kook at other sheets,the ranges chosen will
include the shett numbers/names.You can even look in completely different
work books....
--
paul

remove nospam for email addy!



"Les" wrote:

Hi,

This is a followup question to my previous one which was answered by Stefi
(thanks, Stefi!) on using the INDEX and MATCH command. I'd like to know it
if's possible to use the command to compare a calculated number (C13) against
data located on different sheets and how could that be done?

So, for example, if C13 (on Sheet 1) is calculated as 12.22, this number
would be checked against the data on Sheets 2, 3, and 4. If C13 doesn't
match any of the listed items, the idea is to settle for the closest but
higher number in A1 and return the corresponding info in B1 from all 3 sheets
to Sheet 1. So it would look something like this:

SHEET #1
12.22
d2
c12
j4

SHEET #2
A1 B1
10.83 d1
13.82 d2
16.28 d3
18.47 d4

SHEET #3
A1 B1
21.03 c12
22.36 c13
23.69 c14
25.00 c15

SHEET #4
A1 B1
6.64 j1
9.21 j2
11.35 j3
13.28 j4

Thanks for any help or ideas on this.

Les

Index & Match from Other Sheets?
 

Thanks, Paul. After fidding around a bit with the formula, I got it work.

Les

"paul" wrote:

you can use the same formulas to kook at other sheets,the ranges chosen will
include the shett numbers/names.You can even look in completely different
work books....
--
paul

remove nospam for email addy!



"Les" wrote:

Hi,

This is a followup question to my previous one which was answered by Stefi
(thanks, Stefi!) on using the INDEX and MATCH command. I'd like to know it
if's possible to use the command to compare a calculated number (C13) against
data located on different sheets and how could that be done?

So, for example, if C13 (on Sheet 1) is calculated as 12.22, this number
would be checked against the data on Sheets 2, 3, and 4. If C13 doesn't
match any of the listed items, the idea is to settle for the closest but
higher number in A1 and return the corresponding info in B1 from all 3 sheets
to Sheet 1. So it would look something like this:

SHEET #1
12.22
d2
c12
j4

SHEET #2
A1 B1
10.83 d1
13.82 d2
16.28 d3
18.47 d4

SHEET #3
A1 B1
21.03 c12
22.36 c13
23.69 c14
25.00 c15

SHEET #4
A1 B1
6.64 j1
9.21 j2
11.35 j3
13.28 j4

Thanks for any help or ideas on this.

Les