# Multiple conditions

#### Dorothych

##### New Member
I have 2 lookup values, how do I build it into Vlookup, I have tried =VLOOKUP(AND(B17,C17),Sheet1!\$A\$1:\$C\$10,3,FALSE)
but it did not work.

Please advise if there is other function i could use for multiple lookup values.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
On 2002-09-19 20:19, Dorothych wrote:
I have 2 lookup values, how do I build it into Vlookup, I have tried =VLOOKUP(AND(B17,C17),Sheet1!\$A\$1:\$C\$10,3,FALSE)
but it did not work.

Please advise if there is other function i could use for multiple lookup values.

Your question is not very clear.

one guess try
=VLOOKUP(B17,Sheet1!\$A\$1:\$C\$10,3,FALSE)+VLOOKUP(C17,Sheet1!\$A\$1:\$C\$10,3,FALSE)

Or describe what you require and provde a few line example.

My situation is abit different, I wanted to copy prices from Sheet 2(price list) to sheet 1 by tracing it to Sheet 1 Column a (product) and column b (supplier).

I usually use VLOOKUP for one lookup value ie product but now I need to have supplier as one more value to determine the pricing.

Sheet 2
Cell a1 = Needles, b1 = Brazil, C1 = \$12.00
Cell a2 = Needles, b2 = India, C2 = \$15.00
and so on....

Sheet 1

Cell a1 = needles, b1 = India, c1 = return value extract from Sheet 2.

On 2002-09-19 20:29, Dorothych wrote:
My situation is abit different, I wanted to copy prices from Sheet 2(price list) to sheet 1 by tracing it to Sheet 1 Column a (product) and column b (supplier).

I usually use VLOOKUP for one lookup value ie product but now I need to have supplier as one more value to determine the pricing.

With criteria in cells A1 and B1
=SUMPRODUCT((Sheet2!A2:A10=A1)*(Sheet2!B2:B10=B1)*(Sheet2!C2:C10))

Edit to use your criteria references.
Assumes values show only once per product/supplier on Sheet2
This message was edited by Dave Patton on 2002-09-19 20:43

Tried the formula, no luck, do you have other advice?

thanks

Can anyone help?

On 2002-09-19 23:05, Dorothych wrote:
Tried the formula, no luck, do you have other advice?

thanks

It would help if you had reported the result that obtains...

Let A1:C10 house the price data in Sheet2.

(1.) Select all the relevant cells in Sheet2.
(2.) Go to the Name Box on the Formula Bar.
(3.) Type PriceTable and hit enter.

In Sheet1 in C1 use one of:

[1a]

=INDEX(PriceTable,MATCH(1,(INDEX(PriceTable,0,1)=A1)*(INDEX(PriceTable,0,2)=B1),0),3)

[1b]

=INDEX(Sheet2!\$C\$1:\$C\$10,MATCH(1,(Sheet2!\$A\$1:\$A\$10=A1)*(Sheet2!\$B\$1:\$B\$10=B1),0))

[2]

=INDEX(Sheet2!\$C\$1:\$C\$10,MATCH(A1&"-@-"&B1,Sheet2!\$A\$1:\$A\$10&"-@-"&Sheet2!\$B\$1:\$B\$10,0))

[3] If single record per country and price holds as Dave assumed:

=SUMPRODUCT((INDEX(PriceTable,0,1)=A1)*(INDEX(PriceTable,0,2)=B1),INDEX(PriceTable,0,3))

[1a], [1b], and [2] must be array-entered, that is, you need to hit control+shift+enter at the same time, not just enter.

(1.) Insert a column before the price data in Sheet2.
(2.) In Sheet2 in A1 enter and copy down:

=B1&"-@-"&C1

(3.) Select all the relevant cells including the ones in the new column.
(4.) Go the Name Box on the Formula Bar.
(5.) Type PTable and hit enter.

Now you can use the ordinary formula in Sheet1 in C1:

=INDEX(PTable,MATCH(A1&"-@-"&B1,INDEX(PTable,0,1),0),3)
This message was edited by Aladin Akyurek on 2002-09-20 01:18

YES!!! Its working!!! thanks so much

Replies
1
Views
53
Replies
3
Views
131
Replies
3
Views
89
Replies
4
Views
58
Replies
2
Views
164

1,203,513
Messages
6,055,833
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back