Return more than one value with HLOOKUP

  • Thread starter Thread starter Mr. Smith
  • Start date Start date
M

Mr. Smith

Hi
Is it possible to return more than one value with HLOOKUP?

This is my case:

Row 1 - Dates: | 10.05.03 | 15.05.03 | 20.05.03 | 25.05.03| 01.06.03
Row 2 - Empl: | Mary | John | Tom | Mary | John

With formula
=HLOOKUP("Mary";data;2;FALSE)
Only the first date is returned.
Is it possible to lookup all the dates where Mary are inserted? As an array
or just as seperated values.

If you know of other functions to do this, please let med know.

Mr. Smith
 
{=IF(B2:F2="Mary",B1:F1,"XXXX")}

Replace "XXXX" with whatever is appropriate for your model.

HTH,

Alan.
 
Alan.
Thanks, but it still returns just the first value.
Thing is I do not get the formula to work with the brackets { }
Further I need to use ; instead of , to seperate the statement parts. Is
there something "odd" with my "Add ins" causing this?

This work (returns first value)
=IF(C8:I8="Mary";C7:I7;"XXXX")

This do not work
{=IF(C8:I8="Mary",C7:I7,"XXXX")}

Please let me know if you se something obvious.

Mr. Smith.
 
To work with {} you have to enter the formula with "ctrl+shift+intro" instead just intro. Because the result will be an array you have to select an array too.

AO

----- Mr. Smith wrote: -----

Alan.
Thanks, but it still returns just the first value.
Thing is I do not get the formula to work with the brackets { }
Further I need to use ; instead of , to seperate the statement parts. Is
there something "odd" with my "Add ins" causing this?

This work (returns first value)
=IF(C8:I8="Mary";C7:I7;"XXXX")

This do not work
{=IF(C8:I8="Mary",C7:I7,"XXXX")}

Please let me know if you se something obvious.

Mr. Smith.


 
Back
Top