Golden Age
New Member
- Joined
- Nov 1, 2006
- Messages
- 26
Hello, everyone. Since my last thread that Andrew Fergus solved for me, I've been plugging successfully away still on the exact same project. I have hit another snag. The Screenshot below, shows only a single Column of a Table called CharacterClassSkills. Yes it is a full-fledged Table, complete with the dropdown arrows in Row 1, (though they don't show up in my Screenshot). Each cell under this Table Header, "IQ", has a formula that uses Index and Match to lookup a value.
<tbody>
</tbody>
<tbody>
</tbody>
This is a section of the Table called TableAttributesBonusSaves, (a full-fledged table again), that I am trying to retrieve the sought-after value from. I want the Index/Match Function to find the value, 1, in Cell BFU2.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
This is the the Screenshot showing one of the Match criteria on the Worksheet Stats!.
<tbody>
</tbody>
The formula I can't make work is:
=INDEX(TableAttributeBonusSaves[#All],MATCH(CharacterClassSkills[[#Headers],[IQ]],TableAttributeBonusSaves[[#All],[Attribute]],0),MATCH(Stats!$I$6,TableAttributeBonusSaves[#Headers],0))
It returns the error message "A value is not available to the formula or function". Now I first thought, that there was an issue of the Table Headers being Text and therefore I couldn't use the Match function seeking the number 14 to lookup the desired Column Header because the Headers were Text. So, I left cells BFF1:BFF2 as "General" format, but changed all the cells from BFH1 onward to "Number" format. It didn't change the outcome of the Index Match Function lookup. I still got the same error message.
I then isolated which part of my overall function is producing the error. This Match portion: MATCH(CharacterClassSkills[[#Headers],[IQ]],TableAttributeBonusSaves[[#All],[Attribute]],0) did give me the correct Row for my Index lookup. Fantastic! The error crops up because of this Match portion: MATCH(Stats!$I$6,TableAttributeBonusSaves[#Headers],0). I suspect that it's because of Table Header cell formatting, but I'm at a loss of what else to try other than what I did try already. Does anyone have any ideas to help me out? Thank you in advance.
BZR | |
---|---|
1 | IQ |
2 | #N/A |
3 | #N/A |
<tbody>
</tbody>
Data
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>
This is a section of the Table called TableAttributesBonusSaves, (a full-fledged table again), that I am trying to retrieve the sought-after value from. I want the Index/Match Function to find the value, 1, in Cell BFU2.
BFF | BFG | BFH | BFI | BFJ | BFK | BFL | BFM | BFN | BFO | BFP | BFQ | BFR | BFS | BFT | BFU | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Attribute | Category | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
2 | IQ | 1 | ||||||||||||||
3 | ME | |||||||||||||||
4 | ME |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Data
This is the the Screenshot showing one of the Match criteria on the Worksheet Stats!.
G | H | I | |
---|---|---|---|
5 | |||
6 | IQ | 14 | |
7 |
<tbody>
</tbody>
Stats
The formula I can't make work is:
=INDEX(TableAttributeBonusSaves[#All],MATCH(CharacterClassSkills[[#Headers],[IQ]],TableAttributeBonusSaves[[#All],[Attribute]],0),MATCH(Stats!$I$6,TableAttributeBonusSaves[#Headers],0))
It returns the error message "A value is not available to the formula or function". Now I first thought, that there was an issue of the Table Headers being Text and therefore I couldn't use the Match function seeking the number 14 to lookup the desired Column Header because the Headers were Text. So, I left cells BFF1:BFF2 as "General" format, but changed all the cells from BFH1 onward to "Number" format. It didn't change the outcome of the Index Match Function lookup. I still got the same error message.
I then isolated which part of my overall function is producing the error. This Match portion: MATCH(CharacterClassSkills[[#Headers],[IQ]],TableAttributeBonusSaves[[#All],[Attribute]],0) did give me the correct Row for my Index lookup. Fantastic! The error crops up because of this Match portion: MATCH(Stats!$I$6,TableAttributeBonusSaves[#Headers],0). I suspect that it's because of Table Header cell formatting, but I'm at a loss of what else to try other than what I did try already. Does anyone have any ideas to help me out? Thank you in advance.