HLOOKUP Formula that needs to ajust depending on a selection from a drop down list

**Berridge** · 08-15-2013, 06:14 AM

Hello,

I'm fairly new to LOOKUP formulas and was wodnering if its possible for the formula to be automatically adjusted depending on a selection from a drop down list.

I have attached the ful workbook as all the sheets are linked one way or another..

Firstly, on the "Cover_Sheet" i have used the formula =HLOOKUP($B$1,Margin_Table!$B$1:$S$64,4,0) from B4:B30 which works fine, but when copied to columns C and D it returns "#N/A". The same thing happens when I select a different month from the drop down in B1.

Secondly, the bottom section of the table (row 27 to 30) is returning "0" because of the issue above.

Is it because B1 is a merged cell?

I've scoured google and several forums to no avail! A popular subject that comes up is either nesting HLOOKUP and VLOOKUP or using INDEX-MATCH...something which exceeds my excel knowledge.

Hope you can help,
Tom

**ARGK** · 08-15-2013, 06:32 AM

Hi
In Cell C1 on both the "Cover Sheet" and the "Margin Table" worksheets enter "'January1" (don't forget the apostrophe before the January). Your formulae will then work correctly. Once you have done that change the font colour on both worksheets to white so you don't see the entry in those cells. You can do this for each month.
Hope this helps.
Good luck.
Tony

**Richard Buttrey** · 08-15-2013, 07:03 AM

Hi,

You'd be better advised to use INDEX() & MATCH()

You also need to shift your July - December table on the Margin_Table sheet to V1 so that all months are on the same rows. Then the formula on the cover sheet in B4 copied across and down is

Formula:



=INDEX(Margin_Table!$A$1:$AM$30,MATCH($A4,Margin_Table!$A$1:$A$30,FALSE),MATCH($B$1,Margin_Table!$1:$1,FALSE)+COLUMN()-2)

Yes it's failing currently because there are merged cells, (you should avoid those like the plague since they are often a real nuisance and can compromise other aspects of Excel. Use the Center across selection format instead). Putting January in C1 on the Margin Table sheet would correct it but the C4 cell would need to be changed to C1:C64 and it thereby becomes different to B4. i.e. you can't then copy B4 across.

HLOOKUP Formula that needs to ajust depending on a selection from a drop down list

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

HLOOKUP Formula that needs to ajust depending on a selection from a drop down list

Re: HLOOKUP Formula that needs to ajust depending on a selection from a drop down list

Re: HLOOKUP Formula that needs to ajust depending on a selection from a drop down list

Thread Information

Users Browsing this Thread

Similar Threads

Can a worsheet formula reference a drop down list selection?

HLOOKUP Help. HLOOKUP links to Drop down list problems

[SOLVED] Hide columns depending on a text selection in a drop-down list

[SOLVED] Hide rows depending on a selection in a drop-down list.

Auto fill cells depending on Drop down List selection

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions