In Excel; Can LOOKUP return more than one cell as a result?

Question

Lookup has two different usage:

Lookup( value, array )

and

Lookup( value, lookup_range, result_range )

I want to return more than one cell when it found the value. Like:

=LOOKUP(A17;'Products'!A:A;'Products'!C:D)

C column has its price, D Colum has its currency. And i want to multiply price with local currency to show everything in one currency.

How can i take the results one by one ?

A          B            C           D
Product1   xxxx         10          €
Product2   xxxx         5           $

C and D will return but how can i access them?

Answer 1

Depends on how you want it returned. If you want it returned as a concatenated field you can do something like this:

=LOOKUP(A17;'Products'!A:A;'Products'!C:C)&LOOKUP(A17;'Products'!A:A;'Products'!D:D)

Or if you want to return it into two different cells you could use the two formulas seperately:

=LOOKUP(A17;'Products'!A:A;'Products'!C:C)
=LOOKUP(A17;'Products'!A:A;'Products'!D:D)

Edit: If you tell us a little more about where and how you are storing your exchange rates, we could use those to turn everything into US Dollars also using a lookup.

Answer 2

see if this works =VLOOKUP(A17;'Products'!A:A;'Products'!C) * VLOOKUP(A17;'Products'!A:A;'Products'!D)