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

G

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.

BZR
IQ

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]#N/A[/TD]

</tbody>
Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]BZR2[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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.

BFFBFGBFHBFIBFJBFKBFLBFMBFNBFOBFPBFQBFRBFSBFTBFU
AttributeCategory
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>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FCE4D6"]IQ[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FCE4D6"]ME[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Data​





This is the the Screenshot showing one of the Match criteria on the Worksheet Stats!.

GHI

<tbody>
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]IQ[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "]14[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"][/TD]

</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. :)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
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.
 
Upvote 0
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))
 
Upvote 0
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. :bow:
 
Upvote 0
Golden Age said:
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. :bow:
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
This seems like a numeric error to me, but does anybody have an explanation.
Replies
2
Views
292
TomCon
T
T
  • Question Question
Lookup within a lookup
2
Replies
10
Views
777
TimeForTim
T
H
  • Question Question
Using XLOOKUP in VBA to lookup value underneath specific header name (columns can change depending on worksheet)
Replies
0
Views
166
Hailee994
H
szita2000
  • Question Question
Index Match Match from a named table to a range
Replies
1
Views
1K
Alex Blakenburg
A
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
392
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top