[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Hyline
Newbie

Joined: 24 May 2011
Posts: 1

 Posted: Tue May 24, 2011 2:03 am    Post subject: HLOOKUP I cant get HLOOKUP to work I need to search an array, say A1:E1 for the cell containing a "y" and then return the corresponding value in the array C1:E1. My formula currently looks like this - =HLOOKUP("y";A1:E1;A3:E3;0). Any help would be greatly appreciated. Thanks - Andrew
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

Posted: Tue May 24, 2011 2:20 am    Post subject: Re: HLOOKUP

 Hyline wrote: I cant get HLOOKUP to work I need to search an array, say A1:E1 for the cell containing a "y" and then return the corresponding value in the array C1:E1. My formula currently looks like this - =HLOOKUP("y";A1:E1;A3:E3;0). Any help would be greatly appreciated. Thanks - Andrew
Your formula uses a syntax similar to what would be used with the LOOKUP() function. HLOOKUP()/VLOOKUP()/MATCH() take a single, two dimensional range (table) containing both lookup and return values, unlike LOOKUP() which takes two vectors (lists), one for lookup and one for return value.

Try:
=HLOOKUP("y";A1:E3;3;0)
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

Posted: Tue May 24, 2011 2:33 am    Post subject: Re: HLOOKUP

 Hyline wrote: I need to search an array, say A1:E1 for the cell containing a "y" and then return the corresponding value in the array C1:E1.

Are you sure about that C1:E1?
Also, your formula's second parameter should be a range of cells with the row to be searched for the "y" at the top and at least also the row with the corresponding return value.
The third parameter should be the position number of the row, within the second parameter range, that holds the return values.

Ken Johnson
_________________