Nested VLOOKUP function #N/A error

[jgmhunter]

I have an old sheet that we use to compare values from multiple sites for each month of the year. I am sure there are ways of simplifying the formulas but I am just looking for a way to get it to return a "0" or blank value if the values are not found for a given site for the month selected. Here is the formula I am working with:

=IF($B$1="January",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,4,FALSE),IF($B$1="February",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,5,FALSE),IF($B$1="March",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,6,FALSE),IF($B$1="April",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,7,FALSE),IF($B$1="May",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,8,FALSE),IF($B$1="June",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,9,FALSE),IF($B$1="July",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,10,FALSE),IF($B$1="August",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,11,FALSE),IF($B$1="September",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,12,FALSE),IF($B$1="October",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,13,FALSE),IF($B$1="November",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,14,FALSE),IF($B$1="December",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,15,FALSE),""))))))))))))

I have tried variations of IFERROR or ISERROR and cannot seem to get it right.........
PLEASE HELP!!!

 

[FDibbins]

IF($B$1="January", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,4,FALSE),
IF($B$1="February",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,5,FALSE),
IF($B$1="March", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,6,FALSE),
IF($B$1="April", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,7,FALSE),
IF($B$1="May", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,8,FALSE),
IF($B$1="June", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,9,FALSE),
IF($B$1="July",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,10,FALSE),IF($B$1="August",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,11,FALSE),IF($B$1="September",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,12,FALSE),IF($B$1="October",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,13,FALSE),IF($B$1="November",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,14,FALSE),IF($B$1="December",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,15,FALSE),""))))))))))))

Looking at that, you could probably do that all with just 1 INDEX/MATCH/MATCH

 

[jgmhunter]

IF($B$1="January", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,4,FALSE),
IF($B$1="February",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,5,FALSE),
IF($B$1="March", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,6,FALSE),
IF($B$1="April", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,7,FALSE),
IF($B$1="May", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,8,FALSE),
IF($B$1="June", VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,9,FALSE),
IF($B$1="July",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,10,FALSE),IF($B$1="August",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,11,FALSE),IF($B$1="September",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,12,FALSE),IF($B$1="October",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,13,FALSE),IF($B$1="November",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,14,FALSE),IF($B$1="December",VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,15,FALSE),""))))))))))))

Looking at that, you could probably do that all with just 1 INDEX/MATCH/MATCH

Would that return a #N/A error if there is no value on the sheet?

 

[AhoyNC]

I would agree with Ford that an INDEX / MATCH is probably the way to go, but I think your formula could be changed to:
NOTE: IFERROR function only works in Excel 2010 or later.

=IFERROR(VLOOKUP("310100 SKILLED PRIVATE ROOM & BOARD",OPS,VLOOKUP(B1,{"January",4;"February",5;"March",6;"April",7;"May",8;"June",9;"July",10;"August",11;"September",12;"October",13;"November",14;"December",15},2,0),FALSE),"")

 

[jgmhunter]

The problem is my col index # changes with each different month so that one doesn't work for me.

 

[FDibbins]

See if you can adapt this (and yes, it will return an error if not found)...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Jan[/td][td]feb[/td][td]Mar[/td][td]Apr[/td][td]May[/td][td]Jun[/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]

1​

[/td][td]

10​

[/td][td]

100​

[/td][td]

1000​

[/td][td]

10000​

[/td][td]

100000​

[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]

2​

[/td][td]

20​

[/td][td]

200​

[/td][td]

2000​

[/td][td]

20000​

[/td][td]

200000​

[/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td]

3​

[/td][td]

30​

[/td][td]

300​

[/td][td]

3000​

[/td][td]

30000​

[/td][td]

300000​

[/td][/tr]

[tr][td]

5​

[/td][td]dd[/td][td]

4​

[/td][td]

40​

[/td][td]

400​

[/td][td]

4000​

[/td][td]

40000​

[/td][td]

400000​

[/td][/tr]

[tr][td]

6​

[/td][td]ee[/td][td]

5​

[/td][td]

50​

[/td][td]

500​

[/td][td]

5000​

[/td][td]

50000​

[/td][td]

500000​

[/td][/tr]

[tr][td]

7​

[/td][td]ff[/td][td]

6​

[/td][td]

60​

[/td][td]

600​

[/td][td]

6000​

[/td][td]

60000​

[/td][td]

600000​

[/td][/tr]

[tr][td]

8​

[/td][td]gg[/td][td]

7​

[/td][td]

70​

[/td][td]

700​

[/td][td]

7000​

[/td][td]

70000​

[/td][td]

700000​

[/td][/tr]

[tr][td]

9​

[/td][td]hh[/td][td]

8​

[/td][td]

80​

[/td][td]

800​

[/td][td]

8000​

[/td][td]

80000​

[/td][td]

800000​

[/td][/tr]

[tr][td]

10​

[/td][td]ii[/td][td]

9​

[/td][td]

90​

[/td][td]

900​

[/td][td]

9000​

[/td][td]

90000​

[/td][td]

900000​

[/td][/tr]

[tr][td]

11​

[/td][td]jj[/td][td]

10​

[/td][td]

100​

[/td][td]

1000​

[/td][td]

10000​

[/td][td]

100000​

[/td][td]

1000000​

[/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]Find[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]aa[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]Mar[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]aa[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

20​

[/td][td]Marr[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

21​

[/td][td]

#N/A​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
A17=INDEX($B$2:$G$11,MATCH(A15,$A$2:$A$11,0),MATCH(A16,$B$1:$G$1,0))

A21=INDEX($B$2:$G$11,MATCH(A19,$A$2:$A$11,0),MATCH(A20,$B$1:$G$1,0))
This is what it would look like if no match was found
If you have a version earier than 2007, this will fox that...
=IF(ISERROR(INDEX($B$2:$G$11,MATCH(A19,$A$2:$A$11,0),MATCH(A20,$B$1:$G$1,0))),"",INDEX($B$2:$G$11,MATCH(A19,$A$2:$A$11,0),MATCH(A20,$B$1:$G$1,0)))

If you have 2007 or later...
=IFERROR(INDEX($B$2:$G$11,MATCH(A19,$A$2:$A$11,0),MATCH(A20,$B$1:$G$1,0)))

 

[AhoyNC]

That's what the above formula does. It looks at the month in B1 and changes the column index number to 4,5,6 etc. depending on the month.
You could also use the MATCH function to look for the column header, if you have the months in the header row.