vlookup from multiple columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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(B2: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)
 
JR said:
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
Click to expand...

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,ROW(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
Click to expand...
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)
Click to expand...
....

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))
Click to expand...
....

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.
Click to expand...
....

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(OFFSET(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(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.
 
Tosca said:
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.
Click to expand...

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.
 
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(OFFSET(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(OFFSET(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.
 
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,"",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"),ROW(A1),1)),0,1,,2)

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.
 
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
 
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!!!
 
Back
Top