Basic INDEX(MATCH()) Question:

[Adam Kroger]

I am currently using an INDEX() function to do a basic data retrieval by
specifying both the collumn and rows.
INDEX(p1rn1,1,18)

p1rn1 is C2:AE28

the 1,18 retrieves what is in cell T3
in cell Q:S3 (Cells Q3 -> S3 are merged) is the text string "Weap Dam Rec"

Is it possible to use INDEX(p1rn1,MATCH("Weap Dam Rec",?,?),?) to find the
data in T3, if so, what is the wording of the function? If INDEX() can't do
it, is there anyhting that can?

for the billionth time
Thanks
Adam

 

[Biff]

Hi!

INDEX(p1rn1,1,18)

p1rn1 is C2:AE28

the 1,18 retrieves what is in cell T3

Are you sure about that?

If the named range starts in row 2 (C2), then maybe this will return the
value in T3:

=INDEX(P1M1,2,18)

Either that or the named range is really C3:AE28 or maybe you meant T2?

So, is "Weap Dam Rec" in the first row of the named range?

You could use something like this:

=INDEX(P1M1,1,MATCH("weap dam rec",INDEX(P1M1,1,),0)+3)

But why use something like that if this works:

=INDEX(P1M1,1,18)

Biff

 

[Adam Kroger]

Oops, the range is C3:AE28, I am definately not the best typist. You should
see my posts BEFORE I edit them.

The reason I was hoping to find a way for excel to locate the correct cell
without specifyiong an address is; that the Workbook is still under
construction/evolution, and as I move things around, or figure out new way
to do them, I am having to constantly go back and repair other things.
Additionally, if I can find a cell by some type of lookup, it will solve
another problem I have been having. There are actually about 12 cells
scattered throughout the range I need to find in that manner; in 144
different ranges.

I guess the real question is:
Is there a function that you can use inside INDEX() that will search the
entire range for something, and if so, can you then offset the return either
verticley or horizontally, without having to create a custom function?

 

[Biff]

Hi!

Well, there are several methods that can be used to extract data from a
table but the important thing to keep in mind is that the table needs to be
designed "properly" and that design will usually dictate which method is
best to use.

A good table design is one where the data can be found by definning the
intersection of a row and a column. That's what Index/Match/Match does. You
can also use Vlookup/Hlookup together with Match. You can also use
Offset/Match. You can also use Labels. Sometimes you may be able to use
other functions like Sumproduct to extract data from a table.

So, what it all boils down to is how the table is structured and what type
of data the table holds.

Without some VERY SPECIFIC details about what you're trying to do, it's
impossible to make a specific suggestion.

Biff

 

[Adam Kroger]

So I take it, there is not a function that will search through an entire
array and locat a given String? The data I am trying to retrieve isn't
really set up in a table, more of a form type situation. The table is
below, and above, the area I am looking to retreive these cells. Think of
your phone bill, there is account and personal information at teh top of the
bill arranged in a "visual friendly" manner, and some summary information at
the bottom, with an organized, itemized listing inbetween. That is how this
sheet is arranged. I am trying to recall data form the top and bottom.

 

[Biff]

So I take it, there is not a function that will search through an entire

array and locat a given String? The data I am trying to retrieve isn't
really set up in a table, more of a form type situation.

It's possible.

That is how this sheet is arranged.

If you'd like, I'll take a look at this and see if I can figure out what
you're trying to do. Just let me know how to contact you.

Biff

 

[Adam Kroger]

email addy (e-mail address removed)

It's possible.


If you'd like, I'll take a look at this and see if I can figure out what
you're trying to do. Just let me know how to contact you.

Biff