4 way lookup

[ysherriff]

I need help doing a four way lookup. The tab in question is Post Construction by Month. The data tab is Cognos by Month.

As you can see this is the structure of the raw data. I tried converting to pivot but the way the raw data is structured it is not possible, unless i am missing something.

on the Post Construction by Month tab, i am trending by month based on selection of year and payer type. i am famililar with a three way lookup utilizing concatenate function but not four way lookup.

I believe the solution is sumproduct or sumif with vlookup and/or match but do not know.

any and all help is appreciated.

 

[Smallman]

YSherrif

Your year in the post construction sheet is a number and your years in the other sheet are text. Not the same. Won't work as a result.

I put your data in a fresh file. See the cell in Yellow.

Take care

Smallman

 

[ysherriff]

Thanks smallman. quick question. why are you against named ranges? i understand tables are much better for dynamic ranges but curious why you don't like named ranges.

Thanks

 

[Smallman]

Hi ysherriff

I audit XL spreadsheets and if I look at a spreadsheet and the cell says

=A1 * CPI

I have no idea what CPI is at this stage. I now have to go Ctrl F3 now I see that CPI is in Sheet2 Cell A4. I get out of the name manager and I go to Sheet2!A4 to see what CPI is.

Easier if the cell said

=A1*Sheet2!A4

In a previous post you uploaded a file with 87 named ranges. I know cause I counted them. I teach financial modeling at the Australian Institute of Management (this is just for info not to blow smoke up my bum) and I teach students that up to 6 named ranges is acceptable in a file. After this you are on your own.

You should look at yourself as the custodian of a file, you will not manage the file forever and what you hand over should be instantly understandable by the person receiving the file. Constantly checking named ranges is a genuine pain and hides assumptions.

Named ranges are useful to a point but when they go rogue (like dozens of named ranges in a file) then they are counter productive in my opinion. Others may disagree that is fine but in my experience using them sparingly is the best way to use them.

Hope that helps explain my position.

Take care

Smallman

 

[jeffreyweir]

If I look at a spreadsheet and the cell says

=A1 * CPI

I have no idea what CPI is at this stage. I now have to go Ctrl F3 now I see that CPI is in Sheet2 Cell A4. I get out of the name manager and I go to Sheet2!A4 to see what CPI is.

Easier if the cell said

=A1*Sheet2!A4

I'd argue that if CPI is something likely to be used throughout the file, a named range is the perfect place to store it, rather than hard-coding it into formulas. And I'd argue that assigning the CPI value to a named range is potentially less obfuscating than pointing to a cell range if the choice of a name is a good one.

After all:

• To find out the value of either a named range or a sheet reference, I can simply select that part of the formula and push F9. No need to navigate anywhere.
  
• But to find out the purpose of either a named range or a sheet reference, then in the case of the sheet reference I need to actually visit the cell and look for some description or label - in this case it would no doubt say 'CPI'. Whereas with a well-named name range, I don't.

 

[jeffreyweir]

That said, here's an example where Named Ranges directly impede understanding:
http://forum.chandoo.org/threads/vba-names.19410/#post-118450

 

[Smallman]

I don't say that all named ranged are bad. I say that fewer are better. People who build spreadsheets with lots of named ranges are building burden for other people. I keep my spreadsheets to 6 or less as a general rule. Other people can have a different opinion but less is much better in my opinion.

 

[Smallman]

I am not the only person to hold the views I expressed above. It is interesting to read this:

In the Investment Bankers Handbook (Published)

Despite all of the Advantages of naming cells and ranges too many names cloud the model (a list of 237 named ranges is not easy to use or review)

Which is entirely the point I was trying to make. There will be views which are completely opposite but when given a model with few named ranges and the same model with countless named ranges I know which I would choose every day of the week.

 

[NARAYANK991]

Hi ,

First a disclaimer : I like giving my opinion.

A named range has some benefits :

1. It makes a formula more readable

2. It encapsulates the concept of dynamic ranges ; if a formula were not to use named ranges , and were to incorporate the concept of a dynamic range within it , it would soon become almost incomprehensible.

Of these 2 benefits ( there may be more , but I will discuss just these 2 ) , the first one is :

• Easier to remember when you are constructing the formula
• Easier to comprehend when you are merely going through the formulae

Obviously when you have more than a handful of named ranges , the first advantage disappears ; when you have to modify a formula , say by incorporating an additional criterion , it is irrelevant.

As far as the second one goes , the benefit remains throughout the life of the workbook.

However , if a named range is not dynamic , then not only does the named range not have any benefit , it can be a nuisance.

Suppose we have a formula such as :

=SUMPRODUCT((J1:J9=A1)*(L1:L7=C1))

This will return an error , and it is instantly clear why.

Can we say the same thing about this formula ?

=SUMPRODUCT((Product_Codes=Selected_Code)*(Product_Size=Selected_Size))

Narayan

 

[Haseeb A]

Hello ysherriff,

You can use SUMIF,

C10, then copy down & across.

=SUMIF('Cognos by Month'!$A:$A,$B10,INDEX('Cognos by Month'!$C:$AL,,MATCH($B$6&" "&C$9,'Cognos by Month'!$C$4:$AL$4,0)))