LOOKUP

[tbird]

Is there a way to use one of the LOOKUP functions (or a related function) to return data from multiple cells in the same row instead of just one cell? For instance:
If Col A in a table array contains names and column B contains ages and columns C:F contain other personal data; is there a combination of functions that will report the data in columns C:F as well as column B given a lookup value in col A?

 

[Andrew Poulsom]

You could concatenate the result of several look ups, eg

=VLOOKUP(G1,A1:F10,3)&" "&VLOOKUP(G1,A1:F10,4)

etc

 

[Mark W.]

{=VLOOKUP(G1,A1:F10,{2,3,4,5,6},0)}

You'll want to select 5 adjacent cells on a single row when entering this formula.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[tbird]

Thanks to both Mark & Andrew. Mark, your formula gives me just what I wanted. I can see I need to do some reading up on array formulas! Thanks again.