I would like to look up a value in columnns b,d,f etc and return the
corresponiding value in the adjacent column c,e,g

B C D E F G
2 30000 A 30150 b 30300 c
3 30200 a1 30351 b2 30502 b3

No offense meant but that is a pretty bad layout, you could either use
multiple vlookup functions in an if formula, if there are only 6 columns
then that is probably the better choice, otherwise you could use an array
formula like

=INDEX(A1:G3,MIN(IF(B2:G3=I1,ROW(B2:G3))),MIN(IF(B 2:G3=I1,COLUMN(B2:G3)))+1)

entered with ctrl + shift & enter, note that the index part starts in row 1
and column 1, that way you only have to offset for adjacent column to the
right (the + 1)

--
Regards,

Peo Sjoblom


"JR" wrote in message
...

I would like to look up a value in columnns b,d,f etc and return the
corresponiding value in the adjacent column c,e,g

B C D E F G
2 30000 A 30150 b 30300 c
3 30200 a1 30351 b2 30502 b3

JR wrote:
I would like to look up a value in columnns b,d,f etc and return the
corresponiding value in the adjacent column c,e,g

B C D E F G
2 30000 A 30150 b 30300 c
3 30200 a1 30351 b2 30502 b3


One way, if your data is in a range named "Tbl2" and your lookup value
appears in Cell I2, you could insert the following formula in a cell and
copy down as many rows as there are rows in Tbl2 (I refer to this as the
output range). The sought result(s) will be returned to the cell(s) in
the output range corresponding to the row(s) of the sought value 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) )

This formula assumes that there are no duplicate numbers in B,D,F of any
single row of Tbl2. I haven't considered whether it can be readily
modified to deal with duplicates in a single row.

For any who might use the formulas from the freely downloadable file at
http:/home.pacbell.net/beban, the following formula can be entered in a cell

=OFFSET(INDIRECT(ArrayMatch(I2,Tbl2,"A")),0,1)

This formula assumes that there are no duplicate numbers in Columns
B,D,F. If there are it can be modified to

=OFFSET(INDIRECT(INDEX(ArrayMatch(I2,Tbl2,"A"),n,1 )),0,1)

where n is the number of the occurrence of the lookup value, counting
across the first row of Tbl2 left to right, then down to the next row
and continuing left to right, etc.

Alan Beban

Peo Sjoblom wrote...
No offense meant but that is a pretty bad layout, you could either use

multiple vlookup functions in an if formula, if there are only 6
columns
then that is probably the better choice, otherwise you could use an
array
formula like

=INDEX(A1:G3,MIN(IF(B2:G3=I1,ROW(B2:G3))),MIN(IF( B2:G3=I1,COLUMN(B2:G3)))+1)
....

Your formula assumes there could be multiple instances of the lookup
value, but it won't necessarily work if there are. Given sample data

11 a 22 b 33 c
44 d 33 e 66 f

and the lookup value were 33, your formula would return 'b' rather than
either 'c' or 'e'.

I'll address this in my response to Alan Beban.

Alan Beban wrote...
....
=IF(ISNA(INDEX(INDEX(Tbl2,ROW(A1),0),1,MATCH($I$2 ,INDEX(Tbl2,
ROW(A1),0),0)+1)),"",INDEX(INDEX(Tbl2,ROW(A1),0), 1,
MATCH($I$2,INDEX(Tbl2,ROW(A1),0),0)+1))
....

The ISNA(INDEX(INDEX(..))) term could be replaced by a more
efficient equivalent check.

COUNTIF(INDEX(Tbl2,ROW(A1),0),$I$2)=0

This formula assumes that there are no duplicate numbers in B,D,F of
any
single row of Tbl2. I haven't considered whether it can be readily
modified to deal with duplicates in a single row.
....

Since the data source is a range, it can be dealt with using COUNTIF,
OFFSET and MATCH. If the search should be by row then by group of
columns,
so go down the 1st col, then wrap to the top of the 3rd col, etc., try
the
array formula

=VLOOKUP(I2,OFFSET(Tbl2,0,(MATCH(TRUE,COUNTIF(OFFS ET(Tbl2,0,{0,2,4},,1),
I2)0,0)-1)*2,,2),2,0)

On the other hand, if the search should be by column group then row, so
go
through the column groups on the top row, then wrap to the 1st column
group
on the 2nd row, etc., then this can't be done with a single formula
because
it requires too many levels of nested function calls. However, if the
data
range isn't too large, then it can be done using a defined name like
seq
referring to an expression like

=ROW(INDIRECT("1:1024"))

in an array formula like

=VLOOKUP(I2,OFFSET(Tbl2,0,MOD(MATCH(TRUE,N(OFFSET( Tbl2,INT((seq-1)/3),
MOD(seq-1,3)*2,1,1))=I2,0)-1,3)*2,,2),2,0)

These formulas assume the entries in the 1st, 3rd and 5th cols of Tbl2
are
numbers rather than text. If they're all text, replace the N() calls
with
T() calls. If they're a mix of text and numbers, there's no alternative
to
rearranging the data or using udfs.

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(OFF SET(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.

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(OFF SET(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

Hi Alan

No, the values in A:A, D:D, G:G, J:J, M:M, P:P, S:S etc. are unique and
these are the columns that are being used for the search (there may be many
triple columns of data). Column A might have data such as ADG - 14, ADG -
26, ADG - 52 whilst column D might have data such as HRD - 2, HRD - 65,
HRD - 78 and column G have data such as CBP - 45, CBP - 74, CBP - 123. The
data in corresponding columns B:B, C:C, E:E, F:F, H:H, I:I, K:K, L:L, N:N,
O:O, Q:Q, R:R, T:T and U:U may have duplicates and this is guaranteed in the
third column of each triple as this is a date (just the year) column. I
doubt that this should matter because it is from the second and third
columns that data are being derived to fill specific cells.

I hope that this additional information helps you to help me! I thought is
more sensible to continue my questions in this thread, rather than the one
that I started, because the array formula which Harlan produced works,
albeit for only pairs of columns. I think that the {0,2,4} in the formula
needs to be changed to get data from the second and third columns of my
data. I've tried to change it but it doesn't work so I suspect that
something else has to be changed too.

Thanks again for your time.

"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(OFF SET(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

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(OFF SET(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

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

Thank you Alan for the rapid response. I've just had a quick "play" and
can't get it to work as I need - but I'll persist!

Thank you once again.

"Alan Beban" wrote in message
...
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

I don't know why that would be, but if you'd like to furnish an email
address I'll send you my email address so you could email me a samplce
file if you'd like.

Alan Beban

Tosca wrote:
Thank you Alan for the rapid response. I've just had a quick "play" and
can't get it to work as I need - but I'll persist!

Thank you once again.

"Alan Beban" wrote in message
...

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 ,ROW(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,"",IND EX(INDEX(Tbl2,ROW(A1),0),1,MATCH($I$2,INDEX(Tbl2,R OW(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") ,ROW(A1),1)),0,1,,2)

Alan Beban

Hi Alan

Thank you for the offer. My e-mail address is IainWhitXXXathotmail.com.
Replace the <XXX with <169 and the <at with <@. I hope that this foils
the e-mail address scavenging malware!!!

"Alan Beban" wrote in message
...
I don't know why that would be, but if you'd like to furnish an email
address I'll send you my email address so you could email me a samplce file
if you'd like.

Alan Beban