ExcelBanter - vlookup multiple variables

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - vlookup multiple variables (https://www.excelbanter.com/excel-discussion-misc-queries/197545-vlookup-multiple-variables.html)

vlookup multiple variables

i have two workbooks BOOKA and BOOKB. In BOOKA, i am using the following
formula: =IF(B4="","",VLOOKUP(B4,'BOOKB'!B5:C20,2,FALSE))

In BookA, the data is as follows:
COLUMNA COLUMNB COLUMNC
Store 16 Joe formula will be here
Store 16 Bob ....

In BOOKB, the data is as follows:
COLUMNA COLUMNB COLUMNC
Store 16 Joe 45
Store 17 Joe 32
Store 16 Bob 18

I need the formula in BOOKA to return the value from BOOKB, if the Store
matches and the name is present. the value returned would be 45 if the
lookup criteria was Store 16 and Joe or 32 if the lookup criteria was Store
17 and Joe.

thanx,

jat

vlookup multiple variables

On Aug 5, 8:50 am, jatman wrote:
i have two workbooks BOOKA and BOOKB. In BOOKA, i am using the following
formula: =IF(B4="","",VLOOKUP(B4,'BOOKB'!B5:C20,2,FALSE))

In BookA, the data is as follows:
COLUMNA COLUMNB COLUMNC
Store 16 Joe formula will be here
Store 16 Bob ....

In BOOKB, the data is as follows:
COLUMNA COLUMNB COLUMNC
Store 16 Joe 45
Store 17 Joe 32
Store 16 Bob 18

I need the formula in BOOKA to return the value from BOOKB, if the Store
matches and the name is present. the value returned would be 45 if the
lookup criteria was Store 16 and Joe or 32 if the lookup criteria was Store
17 and Joe.

thanx,

jat

=SUMPRODUCT(--([BookB.xls]Sheet1!$A$1:$A$3=A1),--([BookB.xls]Sheet1!$B
$1:$B$3=B1),[BookB.xls]Sheet1!$C$1:$C$3)

Ken Johnson