Need Excel help

[wilebill]

Hopefully there's an Excel guru or 2 around here. I'm not bad at it, but I've hit a brick wall on a function that I need but can't find. Here's what I need help with.

I've got 2 rows and 11 columns of data that I need to do a lookup on the first row and extract the data from the cell below that matches the lookup. Problem is, the data in the first row is not ordered and "hlookup" or just plain "lookup" won't seem to work unless the data in the rows are ordered.

Here's what the 2 rows look like:
<table x:str="" style="border-collapse: collapse; width: 528pt;" border="0" cellpadding="0" cellspacing="0" width="704"><col style="width: 48pt;" span="11" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" x:bool="FALSE" align="center" height="20" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="TRUE" align="center" width="64">TRUE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> <td style="width: 48pt;" x:bool="FALSE" align="center" width="64">FALSE</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl22" style="height: 15pt;" height="20"> 1</td> <td class="xl22" style="border-left: medium none;"> 2
</td> <td class="xl22" style="border-left: medium none;"> 3</td> <td class="xl23" style="border-left: medium none;"> 4</td> <td class="xl22" style="border-left: medium none;"> 5</td> <td class="xl22" style="border-left: medium none;"> 6</td> <td class="xl22" style="border-left: medium none;"> 7</td> <td class="xl22" style="border-left: medium none;"> 8
</td> <td class="xl22" style="border-left: medium none;"> 9
</td> <td class="xl22" style="border-left: medium none;"> 10
</td> <td class="xl22" style="border-left: medium none;"> 11
</td> </tr> </tbody></table>
What I want to do is to look on the first row until it finds "true" and then extract the data in the cell beneath that (in this case "4") and insert it into the cell that holds the formula. Different scenarios will have "true" in different cells because that is calculated on several other conditions, but only one of the cells on the first row will ever have "true" in it at any given time.

Seems like it ought to be easy to do: look on this row till you find true, then give me the data in the cell under that. But I can't find a function that does anything simple like that.

Any help? Oh, and as an aside I'm using Office 2003, which sometimes acts wonky so maybe it's just in a wonky mood and hlookup should actually work with this.

[Conveyor Belt]

You mean that stupid little paper clip can't help you out???

[wilebill]

You mean that stupid little paper clip can't help you out???I killed that annoying little bastard.

And his stupid pet dog.

[dollfus46]

I killed that annoying little bastard.

And his stupid pet dog.

Heh heh heh. That's funny. I'm not that far along in Excel to help you. Been learning it from a book. Decided to go to Tech and take it in Jan., so I can't be of any help. I do love spread sheets though. Lemme look in the book and see what it says. I"ll get back to you.

[carsalesguy]

does the "ctrl- f" for find not work? i figure you could "find" the word true and go from there......

[aaron]

I never use Office for anything these days. I also didn't know the data had to be ordered in HLOOKUP.

[dollfus46]

Nope, Wilebill, waaaaaaaay over my head. It's about filtering though, isn't it? Wouldn't you set up a criteria range for filtering. Could you use TRUE as the criteria? But beyond that, I wouldn't have a clue. Hang in there until January and I'll ask a teckie. Sorry.

[Wayward]

Checking out an idea, wilebill. I'll be back with a success or failure report.

:smt006

[Wayward]

wilebill, have you tried the vlookup and hlookup functions? I think hlookup might work for your situation. These functions allow you to find an exact match to your lookup value without sorting (ordering) the lookup table.

Some details here (http://support.microsoft.com/kb/181213/).

Gotta head for grandson soccer right now so I can't give your specific example a quick test. For interest, I'll probably play around with it a little later today.

[wilebill]

Well, I guess it was just Excel acting wonky, because now I got it to work with the hlookup function. Or maybe I just forgot to use the "false" parameter as a value at the end, but I could have sworn I tried that several times.

To wayward: I did try the hlookup function several times and it would only return the value of the last cell in the second row. I assumed it was because the table wasn't ordered like it says it has to be in the help section.

Anyway, after several hours of trying it's now working. Thanks for the replies.

[Wayward]

Well, I guess it was just Excel acting wonky, because now I got it to work with the hlookup function. Or maybe I just forgot to use the "false" parameter as a value at the end, but I could have sworn I tried that several times.
Hmmmmm. Excel acting wonky? Sure it wasn't wilebill acting wonky? :smt118 :)

To wayward: I did try the hlookup function several times and it would only return the value of the last cell in the second row. I assumed it was because the table wasn't ordered like it says it has to be in the help section.
Shouldn't be necessary to order the table using hlookup ... according the MS Support link I did in an earlier post. From the link ...

SUMMARY

The VLOOKUP and HLOOKUP functions contain an argument called range_lookup that allows you to find an exact match to your lookup value without sorting the lookup table. Note: It is not necessary to sort the lookup table if you use the range_lookup argument correctly.
I assume it's working correctly without ordering the array? Just curious.

Anyway, after several hours of trying it's now working. Thanks for the replies.
Great. Sounds like an interesting project.

:smt038

[wilebill]

Hmmmmm. Excel acting wonky? Sure it wasn't wilebill acting wonky? :smt118 :) No, it does it to me from time to time. I'll type in a function, even a very simple one, and the program refuses to recognize it as a function, it treats it as simple text. I have to just keep screwing with the formula - deleting things, changing things, etc. - and it will eventually recognize it as a formula. There's no rhyme or reason as to when or why it does that.

Shouldn't be necessary to order the table using hlookup ... according the MS Support link I did in an earlier post. From the link ... If you use the "false" parameter at the end. I know I tried that, and it wouldn't work. But it eventually did. Go figure.

I assume it's working correctly without ordering the array? Just curious.Yeah, finally.

Great. Sounds like an interesting project. It's a really complicated spreadsheet, but hopefully it will make some things at work so much easier for me and others who use it.

Now I've got one more part to do, which is probably the most complicated thing to design, and it will be perfect.

The whole thing probably is better suited for Visual Basic, but I don't know how to do that and don't want to learn.

[Wayward]

The whole thing probably is better suited for Visual Basic, but I don't know how to do that and don't want to learn.
Agreed. Never tackled Visual Basic. Excel spreadsheets and Access databases are about max for me. :)