Tricky dynamic HLOOKUP (Or better way do to it??)

**bluepenguin** · 08-29-2007, 03:21 PM

Hi guys,

Maybe this problem will intrigue some great minds here

I run a web query to populate a table in my spreadsheet. The data is dynamic - and on some days, I will get a couple of extra rows in the table (its weather data, so e.g. when its raining, they will forecast an extra row of how many mm, etc...)

To give a simple example, right now I use Hlookup to locate the value of F3 (the date) in my dynamic range of B26:G26 and step down 3 rows, where my needed temperature values are for that corresponding day. This works great like this already: =HLOOKUP(F3,B26:G29,3,FALSE)

The big issue is...what happens if my target values are not always 3 rows down? If the query refreshes with any extra rows, this reference is shifted. (Well, my starting "Anchor" is shifted)

Any suggestions? Maybe there is a very simple solution, and I'm just missing it. Much much much appreciated!

**NBVC** · 08-29-2007, 03:33 PM

Is it always the last row of data you're after? i.e. after that there are blank rows?

**Dav** · 08-29-2007, 03:36 PM

You need to post a sample of the data for a day when it is raining and a day when it is not to let us have a better idea of what you want.

Regards

Dav

**bluepenguin** · 08-29-2007, 04:12 PM

Here it is, with notes inside!

**bluepenguin** · 08-29-2007, 04:13 PM

Originally Posted by NBVC

Is it always the last row of data you're after? i.e. after that there are blank rows?

No unfortunately, I think its a bit more complicated.. please see the attachment.

**NBVC** · 08-29-2007, 04:27 PM

Would something like this in C4, copied down 2 rows and across 6 columns work?

=INDEX($A$22:$G$30,MATCH($B4,$A$22:$A$30,0),MATCH(C$3,$A$23:$G$23,0))

Edit: So the formula will look for whatever row have the words "High" and "Low" to get its results....

**bluepenguin** · 08-29-2007, 04:46 PM

WOW, thank you thank you thank you thank you!!!!!

**NBVC** · 08-29-2007, 04:57 PM

Originally Posted by bluepenguin

WOW, thank you thank you thank you thank you!!!!!

You're very welcome!

**bluepenguin** · 09-05-2007, 04:11 PM

NBVC - Sorry to have to resurrect this. There is one issue with this function.

=INDEX($A$25:$G$32,MATCH($B4,$A$25:$A$32,0),MATCH( E$3,$A$26:$G$26,0))

The date values will not always be in row 26! When the web query updated, it just threw it into row 25, and it didn't work.. I tried setting the last Match to have an "OR row 25" but it didn't work.

I'm sure there is a solution to this - again, very much appreciated!

**NBVC** · 09-05-2007, 04:18 PM

If it will always be either row 25 or row 26, then try:

Please Login or Register  to view this content.

**bluepenguin** · 09-05-2007, 04:24 PM

I think that its either, or.

LOL - that's a scary looking formula. Thanks for your help.

**NBVC** · 09-05-2007, 04:31 PM

No problem...

It's basically the same formula twice put into an IF() statement ...which checks to see if the dates are firstly in row 25. If the dates are there it executes the middle part of the formula which uses row 25 in the Match() portion you referenced. Otherwise it will execute the last part which uses row 26 for dates...