Results 1 to 8 of 8

20010412, 13:19 #1
 Join Date
 Jan 2001
 Location
 Baltimore, MD, Maryland, USA
 Posts
 254
 Thanks
 0
 Thanked 0 Times in 0 Posts
using Match & Index Functions alone & together
Hi Everyone:
XL 97 HELP for INDEX Function says:
"INDEX({1,2;3,4},0,2) equals {2;4}" BUT when I copy and paste this into my WS, the answer comes up as 2 regardless of whether I enter it as an array formula or not.
Also, the following equation: =MATCH("ccc",B3:F3,0) yields 1, when ccc is in cell B3? What do I use if I want to return a row # rather than a column number?
Also, suppose you have an array, say in cells B3:G3 and one of the values in the array is duplicated in cell A1. Then, what should MATCH(A1,B3:G3,0)) return? Should it return the cell address within the array.
All the above is prelude to my problem, which is that I am trying to get something like the following to work:
INDEX(B$1:F$1,???,0) where in place of "???" I would have an equivalent function to MATCH(H3,B3:G3,0) that would return the row # for which I want INDEX() to return the vector that is in that row(within my array, of course).
Thanks for any help
<img src=/S/blank.gif border=0 alt=blank width=15 height=15>

20010412, 19:30 #2
 Join Date
 Dec 2000
 Location
 eastern Connecticut, Connecticut, USA
 Posts
 113
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using Match & Index Functions alone & together
The only way the Index example will work, is if you select the number of cells = to the number of expected returned values (in this case two) and array enter the formula in this range. It returns 2 in the first cell and 4 in the second.
Your first Match example works as described  Match returns the 'position' within the array or range so if you want to interpret it as a 'ROW' number, place your values in a Column starting in Row #1.
Your problem as stated is a bit confusing since you have six elements in your Match range and 5 in your Index range, but you've indicated you want to return a vector row from an array. Where is this array?

20010413, 04:55 #3
 Join Date
 Jan 2001
 Location
 Baltimore, MD, Maryland, USA
 Posts
 254
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using Match & Index Functions alone & together
HiGary:
Let me try some values for range D3:G5 and lets say we have ccc entered as a criteria in A10
aaasmith,Johnaddress01Phone01
bbbbrown,Charlesaddress02Phone02
cccKnight,Gladysaddress03phone03
My equations would not work if I use "ccc" but they do if I use the ref A10 instead. The eq MATCH(A10,D25,0) returns
3 for the 3rd row of the array and then
=INDEX(D2:G5,MATCH(a10,D25,0),0) returns ccc BUT when I highlight the equation in formula bar and press F9, I get the entire 3rd row part returned in that window. I don't know how to get the row returned to cells so I could use them  your message sounds like you know how to do it but it is late and I won't have time to try it till this weekend.
Oh , I still want to know "what should MATCH(A1,B3:G5,0))  had a typo in my 1st posting  return?". In other words is there a way to return the cell address with the value equal to one placed in cell Thanks againA1?

20010413, 18:32 #4
 Join Date
 Dec 2000
 Location
 eastern Connecticut, Connecticut, USA
 Posts
 113
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using Match & Index Functions alone & together
>Oh , I still want to know "what should MATCH(A1,B3:G5,0)) > had a typo in my 1st posting  return?".
Apparently, #N/A
Although the Help says:
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array can be an array or an array reference.
It looks like they're definition of contiguous means in a continuous line  within a single row or single column. It appears that MATCH, despite calling the second argument "lookup_array", does not like an array for its input
This is all from Excel8 SR2

20010414, 00:58 #5
 Join Date
 Jan 2001
 Location
 St. George, Maine, USA
 Posts
 158
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using Match & Index Functions alone & together
There are a number of functions in Excel that require a set of cells to be selected to house the results of the function.
From the help on the INDEX function reread the following:
If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells. To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for Windows or +ENTER in Microsoft Excel 97 for the Macintosh.
Because your formula: " =INDEX(D2:G5,Match(a10,D25,0),0)"
has a zero for the colum_num the result expected is for a maximum of 4 values.
If you enter the formula in cell F1, doing a CTRL+SHIFT+ENTER, this results in "ccc" in F1.
If you then select cells F1 and F2 (in that order), click in formula bar and do a CTRL+SHIFT+ENTER then F1 and F2 will be filled with "ccc" and "Knight,Gladys" respectively.
If you had selected cells F1 thru F4, then all 4 cells from the data table will be returned.
You asked for a way to acquire the cell address with the value equal to one placed in cell A10.
Well the following will develop the address of the cell containing "ccc" or the value in A10.
="D"&TEXT(2+MATCH(A10,D35,0),0)
With the sample data the result is the text string "D5"
To use this address, become familiar with the INDIRECT function.
Hope this helps.

20010427, 00:45 #6
 Join Date
 Jan 2001
 Location
 Baltimore, MD, Maryland, USA
 Posts
 254
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using Match & Index Functions alone & together
Bob:
I don't think I replied to you. Anyway, I want to thank you for the help. It opened up some doors for me in the application I was working on and I have continued to use these functions in other work.
Thanks

20010427, 06:45 #7
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using Match & Index Functions alone & together
And of course you'd get the same functionality using VLOOKUP:
=VLOOKUP(A10,D2:G4,1,FALSE) returns the aaa,bbb,ccc
=VLOOKUP(A10,D2:G4,2,FALSE) returns smith, brown, ..
=VLOOKUP(A10,D2:G4,3,FALSE) returns address01, ....
=VLOOKUP(A10,D2:G4,4,FALSE) returns Phone01,....Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20010427, 11:49 #8
 Join Date
 Jan 2001
 Location
 Baltimore, MD, Maryland, USA
 Posts
 254
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using Match & Index Functions alone & together
Jan:
I think you may have read the post too quickly. The data are all in separate cells and as far as know VLOOKUP can only return one cell at a time.