Solved: dual lookup problem

[koala]

Hi Forum,

I am not sure if this is the right forum for this question or not.

I want a formula in cell B5 so that it firstly looks at the value in cell H1, then at the value in cell H5. It then looks at a table on a sheet called "DATA", to find the H1 value somewhere in row 1, then looks down that column until it finds the value that was in H5 on the previous sheet. When found, the value that is in column IV on the table is returned to cell B5 of the original sheet.

This needs to be repeated for cell B7 (looking up H1 and H7), then the same table on "DATA" sheet, then B9 and so on.

I can hard code the formula into each cell, B5, B7 etc.

Alternatively if possible I might be able to run a macro that would update each day when I ran it. The only thing that would change each day is the value in cell H1.

H5, H7 etc, and the table on the "DATA sheet always remain the same.

Can anyone please assist, and if this is not the right forum, could you please advise where I should post this.

cheers
Koala

[XLGibbs]

Yes, this can be solved...can you attached a sanitized file to make it easier?

The formula you need is a SUMPRODUCT formula, but without seeing the data structure, I would not want to complicate issues by posting the possible answer incorrectly..

Or...

How many rows is the data table?

Is it every other row starting in B5 that gets the formula ?

[Bob Phillips]

Try this



=INDEX(Data!IV:IV,MATCH(H5,OFFSET(INDIRECT("Data!"&ADDRESS(1,MATCH(H$1,Data!$1:$1,0))),0,0,1000),0))

[XLGibbs]

Dang XLD, that is mighty Aladin like of you!

very nice indeed....

[Bob Phillips]

Dang XLD, that is mighty Aladin like of you!

very nice indeed....

I looked at it this morning but didn't crack it then, but was moved to have another go when you said it would be an SP solution, as I didn't see it that way.

[XLGibbs]

I agree, particularly since the alternating row issue....but I was hoping to see dataset to look at...at first glance, it seemed like SUMPRODUCT, but I was waiting...I still struggle somewhat with the Index/Match logic sometimes so it is rarely my first thought....

Hopefully the OP comes back into the equation here...I would bet yours would work fine, and likely more efficiently than the SP method (and much easier to implement)

[koala]

Hi forum,

Thanks for your replies and my appologies for not getting back sooner, however I have been away for a few days.

I managed to solve this by firstly creating a small table away on the side on my sheet, doing a hlookup on the DATA sheet to populate the table, then using Vlookup in my main data to get the value.

I will however try out your responses to see if they make my sheets "cleaner"

thanks for your help
Koala