In my first response in this thread (which I copied to you in your other
thread) I indicated the following for pairs of rows rather than triples,
to be array entered into a cell and copied down as many rows as there
are rows in Tbl2:
=IF(ISNA(INDEX(INDEX(Tbl2,ROW(A1),0),1,MATCH($I$2, INDEX(Tbl2,ROW(A1),0),0)+1)),"",INDEX(INDEX(Tbl2,R OW(A1),0),1,MATCH($I$2,INDEX(Tbl2,ROW(A1),0),0)+1) )
For triples, if you simply change the final 1 to {1,2} and array enter
it into a 2-cell row before copying it down, it will produce your
desired result.
Harlan Grove suggested that the ISNA. . . portion of the formula be
replaced by COUNTIF(INDEX(Tbl2,ROW(A1),0),$I$2)=0, producing
=IF(COUNTIF(INDEX(Tbl2,ROW(A1),0),$I$2)=0,"",INDEX (INDEX(Tbl2,ROW(A1),0),1,MATCH($I$2,INDEX(Tbl2,ROW (A1),0),0)+{1,2}))
Note that the formula does not depend on the number of sets of triples
(although you would obviously have to change the cell where the lookup
value resides, $I$2 in this version of the formula, if your data
extended out to include that cell); that is, it does not depend on the
number of columns in Tbl2. Nor does it depend on the number of rows in Tbl2.
The equivalent formula for those who use the functions from the freely
downloadable file at http:/home.pacbell.net/beban is the following,
array entered into a 2-cell row:
=OFFSET(INDIRECT(INDEX(ArrayMatch($I$2,Tbl2,"A"),R OW(A1),1)),0,1,,2)
Alan Beban
Tosca wrote:
Hi Alan (again!)
I've been playing with the formula and have managed to get it to work with
four triple columns of data. The index column for the search is the first
and the data retrieved from the cell immediately to the right is:
=VLOOKUP(I2,OFFSET(Tbl2,0,(MATCH(TRUE,COUNTIF(OFFS ET(Tbl2,0,{0,3,6,9},,1),I2)0,0)-1)*3,,2),2,0)
whilst that from two cells to the right is:
=VLOOKUP(I2,OFFSET(Tbl2,0,(MATCH(TRUE,COUNTIF(OFFS ET(Tbl2,0,{0,3,6,9},,1),I2)0,0)-1)*3,,3),3,0)
This is fine, but I don't want to have to change the {0,3,6,9} manually to,
for instance {0,3,6,9,12,15,18}. I know that "=COLUMNS(Tbl2)" will return
the number of columns in Tbl2, but I don't know how the relevant numbers can
be entered between the { and } automatically into OFFSET. Can this be done?
Thank you.
"Alan Beban" wrote in message
...
Tosca wrote:
Hi Harlan
By coincidence, I posted a similar problem and Alan pointed me to this
thread. The array formula which you produced
"=VLOOKUP(I2,OFFSET(Tbl2,0,(MATCH(TRUE,COUNTIF( OFFSET(Tbl2,0,{0,2,4},,1),
I2)0,0)-1)*2,,2),2,0)" worked perfectly if there are series of pairs of
columns of data (say, columns A, C and E) which are used for the search
and the corresponding data to be retrieved is one cell to the right in
columns B, D and F. The problem that I have is that I have data in three
columns, A, B and C are related as are D, E and F. In other words, I
need to be able to find a unique occurrence (there will be no duplicates
in the data to be sought) in column A or D and then return the
appropriate information from one and two cells to the right into separate
cells in the spreadsheet. How would I modify your array formula to
account for this?
A final problem is that there may be 20, 30 or more sets of data, each
with three related columns. I suspect that I could set the whole sheet
as a named range <Tbl2. Is this correct?
Many thanks for your time.
Might any of the values in B:C duplicate values in A:A?
Alan Beban
|