Multiple conditions

[Dorothych]

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.

 

[PaddyD]

Hi - welcome to the board!

see here:

http://www.mrexcel.com/board/viewtopic.php?topic=21288&forum=2

for a few options. Post back if you need more!
Paddy

 

[Dave Patton]

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.

 

[Dorothych]

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.

 

[Dorothych]

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.

 

[Dave Patton]

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

 

[Dorothych]

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

thanks

 

[Dorothych]

Can anyone help?

 

[Aladin Akyurek]

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.

Addendum. An additional method would be the following.

(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

 

[Dorothych]

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