(H/V)LOOKUP Function

Posted by Mike Levine on September 04, 2001 4:43 AM

Dear Mr Excel

I've been struggling with a particular challege that my boss has given me and was wondering if you could offer any advice.

I want to search each row for the occurrance of the character "F" and return the date, from the top row of the respective column into another cell.

The Letter "C" is always in a cell before any other letters in each row and one cell, to the right of the C, may contain "LF" or "OLF" or "LOF" or "F". When I use LOOKUP the function returns the date in the column with C, rather than the correct column that the letter "F" occurrs in (with the exception of "F" on its own, which the function works perfectly).

Here's an example of the formula used:
=LOOKUP("F",E2:X2,$E$1:$X$1)

When the cell contains just the letter F the function works correctly. This tells me that using LOOKUP to check for the occurrance of a single letter amongst many in a cell doesn't work.

What function will? I've tried H&V Lookup and I don't seem to be getting anywhere.

Your help would be greatly appreciated.

Kind Regards
Mike Levine

Posted by Aladin Akyurek on September 04, 2001 6:55 AM

Does...

F occur once or more in a given row?

Aladin

=================

Posted by Mike Levine on September 04, 2001 7:24 AM

Re: Does...

Aladin
"F" occurs normally once and I will manually deal with other instances.

Have looked at INDEX(MATCH) and the problem, again is that MATCH will not return the correct column number if the exact text is not used as the first arguement.

Thanks for looking into this!

Mike

Posted by Aladin Akyurek on September 04, 2001 7:42 AM

Re: Does...

> "F" occurs normally once and I will manually deal with other instances.

Mike --

I'll assume the following sample which is in A1:C4.

{35489,35490,35491; "C","F","X";"C","LOF","Y";"C","Y","OLF"}

Big numbers are serialized dates, so don't worry about them.

In E2 enter: =IF(SUMPRODUCT((ISNUMBER( SEARCH("F",A2:C2))+0))=1, INDEX($A$1:$C$1, SUMPRODUCT((ISNUMBER( SEARCH("F",A2:C2)))*(COLUMN(A1:C1)))),"?")

Copy down this as far as needed.

This formula will return a question mark if either N("F")=0 or N("F") > 1.

Aladin

==================

Posted by Mike Levine on September 04, 2001 7:59 AM

WOW

Aladin

I'm very impressed!
Perhaps in another year, I would have considered using SUMPRODUCT(ISNUMBER(SEARCH(INDEX))) combination...Wow!

You definately are an Excel expert.

Much appreciated and if you are ever in Dublin I'll buy you a few Extra Cold Guinness, or whatever your preference.

Thanks again!
Mike

Posted by Mike Levine on September 05, 2001 1:05 AM

Side-Effect

Aladin

I'm experiencing a side-effect that you may be able to answer.

If I put the formula to the left of the data the results are offset (to the right of the lookup data) by the number of columns to the left the formula is in.

Using Office 2000 SP2

Mike I'm very impressed!

Posted by Aladin Akyurek on September 05, 2001 1:57 AM

Re: Side-Effect

> If I put the formula to the left of the data the results are offset (to the right of the lookup data) by the number of columns to the left the formula is in.

Mike --

If you inserted a new column A, then you need to adjust the range for INDEX. If unsure, post the formula from its new location.

Aladin

Posted by Mike Levine on September 05, 2001 2:09 AM

Re: Side-Effect

Aladin

I understand that the INDEX would change if a column was inserted.

The side-effects happen despite naming the correct index range.

Something about the formula that doesn't like columns to the left of the defined index.

Mike

Posted by Aladin Akyurek on September 05, 2001 2:23 AM

Re: Side-Effect

Mike

I can't reproduce the phenomenon. Please either post the formula or send me your workbook.

Aladin

Posted by Mike Levine on September 06, 2001 5:48 AM

Solutions

The Problem Reviewed:
A B C E
01-Jan-01 02-Jan-01 03-Jan-01 RETURN VALUES
C O LF 03-Jan-01
COLF (blank) (blank) 01-Jan-01
CO LF (blank) 02-Jan-01

I want to search for all occurences, by row, of the letter "F" (representing [finished]) and populate the return values column (E) with the appropriate date that all "F"'s were detected.

The solution, provided by Aladin (thank-you!), does not include any of the Excel Lookup functions! Amazing as this seems, it's true that the lookup functions fail miserably when trying to do this type of thing.

I'm sure a macro could be developed quickly in VB, but I've not tried that approach as I wanted to use the built-in functions if possible.

The solution requires the use of 4 functions:
SUMPRODUCT
ISNUMBER
SEARCH
INDEX

(NOTE: I'm using Office 2K SP2. I've not tested this on earlier versions or XP, so your results may vary.)

The key to this solution working is identifying the correct INDEX range. This point cannot be more adequately stressed. More on this later.

Create a table with all data in colums A-C (as above)

Format row 1 as dates (dd-mmm-yyyy or preference)

Using the following formula in cell E2:

=IF(SUMPRODUCT((ISNUMBER( SEARCH("F",A2:C2))+0))=1, INDEX($A$1:$C$1, SUMPRODUCT((ISNUMBER( SEARCH("F",A2:C2)))*(COLUMN(A1:C1)))),"?")

Copy down to row E4 and the solution is there!
Excellent formula and kudos to Aladin.

In the case where the data is populated in columns other than A-C is where you need to pay particular attention to the INDEX range.

For example, in the table that you created insert a column in A (source data now moves to B-D), what happens to your results? What would you think you need to change in the formula so that your results were the same? It may be very obvious that the INDEX range needs to be changed, but what would you change it to?

It isn't obvious and I'm am emphasising this for a reason.

The SUMPRODUCT part delivers a column number: if our value is "F" in column C for example, we get 3, which is precisely the column number of "F". INDEX must get the 3rd value from the range A2:D2(thus not from the range B2:D2 which seems natural).

Therefore the formula should be changed to:

=IF(SUMPRODUCT((ISNUMBER( SEARCH("F",B2:D2))+0))=1, INDEX($A$1:$D$1, SUMPRODUCT((ISNUMBER( SEARCH("F",A2:C2)))*(COLUMN(A1:C1)))),"?")

Aladin offers yet another solution that is less troublesome:

=IF(SUMPRODUCT((ISNUMBER( SEARCH("F",B2:D2))+0))=1, INDIRECT(ADDRESS(ROW($2:$2), SUMPRODUCT((ISNUMBER( SEARCH("F",B2:D2)))*(COLUMN(B2:D2))))),"?")

Hope this helps you as much as it helped me!
Thanks again Aladin!

Mike