excell vlook up tables?

Question

does anyone know how to string vlooks together so that i can get multiple results back from a single reference .(column index number) ok..as an example
=VLOOKUP( A1,E:G,10,FALSE)

can i change the column refernce 10, to read 9,10,11,? so that i get 3 sets of data points back? using one refernce lookup..A1?

am i talking sense? by the way the avove formular was just an example..

Answer 1

Are you trying to add or perform an operation on 3 values on the other sheet, and place the result in a cell, or are you asking if you can lookup 3 values in another sheet and place them in 3 cells?

If the former, simply add the vlookups to your expression:
ie: B1 =vlookup(A1,E:G,10,FALSE)+vlookup(A1,E:G,11,FALSE)+vlookup(A1,E:G,12,FALSE)

If the latter, simple repeat the formula in each of the 3 target cells:
ie : B1 = vlookup(A1,E:G,10,FALSE)
C1 = vlookup(A1,E:G,11,FALSE)
D1 = vlookup(A1,E:G,12,FALSE)

Hope this helps.

Answer 2

No, the VLOOKUP and HLOOKUP functions return only one value. But you can do this using the function OFFSET. Suppose you have the following matrix:

1 7 9
2 3 5
3 1 8
4 2 9

Suppose the left upper corner is in cell A1. Suppose you want line 3 and the elements of clolumns 2 and 3, that is, 1 and 8. You can use the following formula:

=OFFSET(A1, 2,1 ; 2) A1 is your reference cell. 2 is the offset of line 3 with respect to line 1 the line of cell A1. 1 is the offset of the second column of the matriz with respect to the first. You put ; to skip a parameter on your list and then put 2, the width of the region you want to capture.

To use this version of OFFSET , you select 2 contiguous cells, put the formula in the 1st cel and close it pressigf Ctrl Shift simultaneously. This is because it's an array formula. And you get the 2 desired values.

If you have questions, since these things are a bit hard to explain here, email me at [e-mail], preferentially, or at [e-mail] I can send you an example spreadsheet, if you do'nt find a betetr solution

Of course, a possibilty is repeating the VLOOKUP. But the OFFSET solution is sometimes more flexible.

Answer 3

you want all the results to show up in one cell?

for example like.....

mens, x-large, red

all three results displayed in one cell?

or are the three result numbers? to be added together?

need more info.