match/index question - Aladin? Help?

[invisigirl]

This may be just a Yes/No question.

Okay, last week or so, I got an AWESOME answer on my question about how to make a lookup worksheet for end users with no Excel knowledge. Here is one of the formulas Aladin gave me (adapted for my use):

=IF(COUNTIF('CPT DATA'!$B:$B,B$6),INDEX('CPT DATA'!$C:$C,MATCH(B$6,'CPT DATA'!$B:$B,0)),"")

What I'm wondering is this: I would like to add four new columns in between the current B and C, and put more numbers in there that the formula would look at when indexing and pulling in the value to the right. However, if I just change the COUNTIF and MATCH ranges to $B:$F and the INDEX range to $G:$G, it doesn't work. Is there a way to have it look across AND down, or not? If not, I will deal with it another way, but it would be much easier if I could go both directions.

If it's going to take a macro or something, I'm not interested. I can't work those blasted things.

Thanks for answering.

[Aladin Akyurek]

On 2002-05-23 10:08, invisigirl wrote:
This may be just a Yes/No question.

Okay, last week or so, I got an AWESOME answer on my question about how to make a lookup worksheet for end users with no Excel knowledge. Here is one of the formulas Aladin gave me (adapted for my use):

=IF(COUNTIF('CPT DATA'!$B:$B,B$6),INDEX('CPT DATA'!$C:$C,MATCH(B$6,'CPT DATA'!$B:$B,0)),"")

What I'm wondering is this: I would like to add four new columns in between the current B and C, and put more numbers in there that the formula would look at when indexing and pulling in the value to the right. However, if I just change the COUNTIF and MATCH ranges to $B:$F and the INDEX range to $G:$G, it doesn't work. Is there a way to have it look across AND down, or not? If not, I will deal with it another way, but it would be much easier if I could go both directions.

If it's going to take a macro or something, I'm not interested. I can't work those blasted things.

Thanks for answering.

MATCH can only look in a single horzontal (e.g., F1:Z1, 1:1) or vertical (B1:B100, B:B) range.

The INDEX/MATCH formula you are using can be expanded if it's possible by the nature of your data to compute on the fly in which column MATCH should look in.

[invisigirl]

I don't think I'm bright enough to figure that out, so I'll just do it another way. But thanks for answering my question. I wondered if that was the issue.

Thanks again!