Search in multiple columns using VLOOKUP?

[George]

Hi,

I'm looking for a formula to find a row in a spreadsheet with two matching
fields which returns the value of a field in a certain column in that row.
VLOOKUP only supports searching in one column as far as I know. Let me give
you an example to clarify my request.

Please read the following text in a non-proportional font like Courier New.

Worksheet A

A B C D
0 4 7
0 3 6
1 0 7
5 0 6

Remarks: either column A or B is always zero, and the other one non-zero. I
need the formula to use the value of the field which is non-zero. Further
on, the value of the column C has to match. We are searching for the value
that needs to be displayed in column D. We are searching in Worksheet B,
which looks like this for example:

Worksheet B

A B C D
0 4 3 1
0 4 7 2
0 4 8 3
0 3 3 4
0 3 7 5
0 3 6 6
1 0 3 7
1 0 7 8
1 0 8 9
5 0 6 10
5 0 4 11
5 0 9 12

Row 1 in worksheet A has cell A=0, so we are searching in worksheet B for a
row where the B column equals 4, AND the C column equals 7. So the formula
has to return the value of the D column of the second row, which is 2.

Row 2 in worksheet A has cell A=0, so we are searching in worksheet B for a
row where the B column equals 3, AND the C column equals 6. So the formula
has to return the value of the D column of the sixth row, which is 6.

Row 3 in worksheet A has cell A=1, so we are searching in worksheet B for a
row where the A column equals 1, AND the C column equals 7. So the formula
has to return the value of the D column of the eigth row, which is 8.

Row 4 in worksheet A has cell A=5, so we are searching in worksheet B for a
row where the A column equals 5, AND the C column equals 6. So the formula
has to return the value of the D column of the tenth row, which is 10.

So with the correct formula for the D column in worksheet A, worksheet A
will look as follows:

Worksheet A

A B C D
0 4 7 2
0 3 6 6
1 0 7 8
5 0 6 10

Could anyone please provide me with the formula for the D column?

Thanks a lot!

George

 

[Frank Kabel]

Hi George

You received a reply to this yesterday. Have you tried it?. Just to
repost my yesterday's comment:
------
first have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm
(search for double lookup)

for your specific problem use the following
=IF(A=0,INDEX('sheet b'!$D$1:$D$99,MATCH(1,('sheet
b'!$B$1:$B$99=B1)*('sheet b'!$C$1:$C$99=C1),0)),[fill in if A<>0])

-------

In respect to your example the formula should look like the following:
=IF(A=0,INDEX('sheet b'!$D$1:$D$99,MATCH(1,('sheet
b'!$B$1:$B$99=B1)*('sheet b'!$C$1:$C$99=C1),0)),INDEX('sheet
b'!$D$1:$D$99,MATCH(1,('sheet
b'!$A$1:$A$99=A1)*('sheet b'!$C$1:$C$99=C1),0)))

not fully tested but it should work. If you have any firther problems
you can mail me your spreadsheet and I'll have a look at it

HTH
Frank

 

[dancing fish]

There is probably a better way of doing this, but you cuold perhaps try
the following:

If you enter a further field which concatenates the values in a,b and c
in worksheet A and does likewise to the values in the lookup table in
worksheet B then you can always use a simple lookup function to return
the correct row into column D of worksheet A.

e.g. field in both worksheets that is:

=concatenate(a1,b1,c1)

if you put this in column e, and in column f, then make a the lookup
table based in columns e and f the value of the row then d could
equal:

=vlookup(e1,Table in Worksheet B,2,0)

This depends if its okay to enter the extra columns.

I think!!

DF.

 

[George]

Hi Frank,

Thanks for your reply. I will check this out. I posted a similar question
yesterday using Google, but couldn't find it anymore, so I posted this
version.

George

 

[George]

Thanks DF, I will check this out.

George