Lookup and retrieve multiple cells

[Bidwin]

Is it possible to retrieve multiple cells from a table with one lookup function? For example, I have a table that contains product information. Column A contains a product code, and Columns B thru E contain pricing information. How can I search for a product code then return the related four columns with one function?
Thanks,
Bidwin

[texasalynn]

Vlookup will return the data based on the product code. You would put the vlookup with the change on the column being returned in each formula,

vlookup(a1,tablelookup,2,0)
vlookup(a1,tablelookup,3,0)
vlookup(a1,tablelookup,4,0)

HTH
texasalynn

[onlyadrafter]

Hello,

Do you mean you want all these cells in one cell after the VLOOKUP, if so then you just need to combine texasalynns formula i.e.

=vlookup(a1,tablelookup,2,0)&" "&vlookup(a1,tablelookup,3,0)&" "&vlookup(a1,tablelookup,4,0)&" "&vlookup(a1,tablelookup,5,0)


the " " just puts a space between the data.

[Bidwin]

Thanks for your replies, Lynn and Drafter. I should have made my question clearer: I want to look up a product code in Column A of a table, retrieve related cells in Columns B thru E, and place the retrieved cells into separate cells, not combine them. My first solution was the same as Lynn's.
Thanks again,
Bidwin

[Aladin Akyurek]

Originally Posted by Bidwin

Thanks for your replies, Lynn and Drafter. I should have made my question clearer: I want to look up a product code in Column A of a table, retrieve related cells in Columns B thru E, and place the retrieved cells into separate cells, not combine them. My first solution was the same as Lynn's.
Thanks again,
Bidwin

Actually, what Alynn proposed provides solves your retrieval problem. What follows would be more efficient...

******** ******************** ************************************************************************>

Microsoft Excel - Book9 ___Running: xl2000 : OS = Windows Windows 2000

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

B2C2D2E2F2B3C3D3E3F3 =

A

B

C

D

E

F

G

H

I

J

K

L

1

*

Position

2

3

4

5

*

Table*Products*&*Prices

2

p1

1

2.3

2.2

1.9

1.87

*

Product

X

Y

Z

W

3

p7

#N/A

*

*

*

*

*

p1

2.3

2.2

1.9

1.87

4

*

*

*

*

*

*

*

p2

3.1

3.7

2.98

3.2

5

*

*

*

*

*

*

*

*

*

*

*

*

Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Name your lookup table, which can be anywhere outside the retrieval area, PriceTable. In the above exhibit, PriceTable refers to: H3:L4.

Formulas...

A1:A2 houses lookup values of interest.

C1:F1 houses column index values that refer to columns within PriceTable from which you want to retrieve appropriate prices.

B2:

=MATCH(A2,$H$3:$H$4,0)

C2, which is copied across...

=IF(ISNA($B2),"",INDEX(PriceTable,$B2,C$1))

Finally, select B2:F2 and copy down.

[Bidwin]

Thanks for the neat solution, Aladin.
Bidwin