VLOOKUP in 2 columns

[samikusi]

I would like to be able to use the VLOOKUP function to search for a value that may be in either of 2 columns

Column A Column B C D E F G

0EA53014-02 12/10/10 12/03/10 12/28/10 1 1
0EA40314-02 11/30/10 12/03/10 11/30/10 1 1
0EA30014-02 11/29/10 12/03/10 12/16/10 1 1
0EA80040-01A 11/23/10 11/19/10 12/16/10 2 2
0EA80040-01B 11/16/10 11/19/10 12/03/10 2 2
024 26513 432 0E250071-01,02 00/00/00 10/15/10 00/00/00 2 0
024 26513 432 0E630088-05 10/29/10 10/15/10 11/22/10 2 2
024 26513 433 10407696-01 10/18/10 10/15/10 11/04/10 2 2
024 26513 433 11405065-01 11/18/10 11/15/10 12/07/10 2 2
024 26513 433 11407771-01 11/09/10 11/05/10 11/19/10 2 2

H1 = 0EA40314-02
H2 = 0E630088-05

VLOOKUP(H1, $A$1:$G$10, 4, False). This searches only the first column of the array for the value in question. I would like to be able to search for the value in both columns A and B without changing the array defined. Any ideas?

[a31185]

=iferror(VLOOKUP(H1, $A$1:$G$10, 4, False),VLOOKUP(H1, $B$1:$G$10, 3, False))

This would first look in column A, if an error is returned, it would look in column B

[samikusi]

Thanks for the reply. I was hoping to accomplish this without defining another array ($B$1:$G$10). Is there any function that will look at two columns at the same time?

[NBVC]

As long is there is one match in either column, you can use SUMPRODUCT...

e.g.

=SUMPRODUCT(($A$1:$B$10=$H1)*(C$1:C$10))

you can copy down for more H values, and across for column D sum, E sum, etc...

[samikusi]

@ NBVC - thanks for the response. in your formula why are you multiplying by array C$1:C$10?

[FDibbins]

its not multiplying, you use the * as an "and" for comparison purposes. see the following link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[NBVC]

@ NBVC - thanks for the response. in your formula why are you multiplying by array C$1:C$10?

The first array which is a conditional check returns an array of TRUE's and FALSE's... each of these is then "multiplied" by the values in C1:C10... a TRUE is coerced to a 1 when multiplying and a False is coerced to a 0... so each TRUE (1) is multiplied by corresponding cell in C1:C10 yielding, obviously, the values in C1:C10, and each FALSE(0) multiplied by the values in C1:C10 yields, obviously, 0... then all those are summed to give final sum based on conditions.... the link FDibbins provided explains Sumproduct() more so you will understand.