using Match & Index Functions alone & together

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

HiGary:
Let me try some values for range D3:G5 and lets say we have ccc entered as a criteria in A10

aaa|smith,John|address01|Phone01
bbb|brown,Charles|address02|Phone02
ccc|Knight,Gladys|address03|phone03

My equations would not work if I use “ccc” but they do if I use the ref A10 instead. The eq MATCH(A10,D2:D5,0) returns
3 for the 3rd row of the array and then
=INDEX(D2:G5,MATCH(a10,D2:D5,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?

Reply | Quote

>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 SR-2

Reply | Quote

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,D2:D5,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,D3:D5,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.

Reply | Quote

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

Reply | Quote

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,….

Reply | Quote

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.

Reply | Quote