Getting a Match Function to Lookup a Numeric Value Within a Range of Table Headers

[Golden Age]

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.

	BZR
1	IQ
2	#N/A
3	#N/A

<tbody>
</tbody>

Data​

Worksheet Formulas Cell Formula BZR2 =INDEX(TableAttributeBonusSaves[#All],MATCH(CharacterClassSkills[[#Headers],[IQ]],TableAttributeBonusSaves[[#All],[Attribute]],0),MATCH(CharacterClassSkills[[#Headers],[Category]],SkillList[#Headers],0),MATCH(Stats!$I$6,TableAttributeBonusSaves[#Headers],0)) <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.

 

[Marcelo Branco]

Not sure i understand what you need - see if this works
=INDEX(TableAttributeBonusSaves[#All],MATCH(CharacterClassSkills[[#Headers],[IQ]],TableAttributeBonusSaves[[#All],[Attribute]],0),MATCH(Stats!$I$6&"",TableAttributeBonusSaves[#Headers],0))

M.

 

[DanteAmor]

The value that you put in the header, when you convert it into a table, excel simply identifies it as text, that's why you should treat it as text.
You must convert the number 14 that you have in cell I6 to text or also the way Marcelo used it.


=INDEX(TableAttributeBonusSaves[#All],MATCH(CharacterClassSkills[[#Headers],[IQ]],TableAttributeBonusSaves[[#All],[Attribute]],0),MATCH(Text(Stats!$I$6, "#"),TableAttributeBonusSaves[#Headers],0))

 

[Golden Age]

My very great thanks to both of you. I tried out both your formulas and they do exactly what I wanted. I was worried that somehow that number 1, I was trying to lookup, would be converted to "text", and consequently couldn't be used as a number in the subsequent SUM formula, but my concerns were unnecessary. I haven't the foggiest idea how the TEXT and "#" features of DanteAmor, and the &"" feature of Marcelo work but I'm going to Google search those to find out. Thank you again. I really appreciate it.

 

[Marcelo Branco]

You are very welcome. Glad we could help

M.

 

[DanteAmor]

My very great thanks to both of you. I tried out both your formulas and they do exactly what I wanted. I was worried that somehow that number 1, I was trying to lookup, would be converted to "text", and consequently couldn't be used as a number in the subsequent SUM formula, but my concerns were unnecessary. I haven't the foggiest idea how the TEXT and "#" features of DanteAmor, and the &"" feature of Marcelo work but I'm going to Google search those to find out. Thank you again. I really appreciate it.

The two options simply convert a numeric value to a text value. In this case the number 14 is still 14 but as a string.

I'm glad to help you. Thanks for the feedback.