Match and Index for Tables
 

Can anyone tell me how to get the match and index functions to work for an
8x8 table?

So for example I would like the function to take a given text string ec for
example, search for its position in the first table, then look at the second
table and return the value the corresponds to the text string located in the
first box.

Examples - I ask the function to find the value corresponding to; ec the
function returns the value 99, for bh - 4, for ga- 78.

The text string to match is in cell m10, and the top left corner of the
tables begins in cell N10.

Tables below

a b c d e f g h
a aa ba ca da ea fa ga ha
b ab bb cb db eb fb gb hb
c ac bc cc dc ec fc gc hc
d ad bd cd dd ed fd gd hd
e ae be ce de ee fe ge he
f af bf cf df ef ff gf hf
g ag bg cg dg eg fg gg hg
h ah bh ch dh eh fh gh hh

a b c d e f g h
a 20 11 30 33 44 40 78 54
b 20 20 20 20 20 20 20 20
c 20 43 5 90 99 67 60 56
d 20 20 20 20 20 20 20 20
e 20 20 20 20 20 20 20 20
f 20 20 20 20 20 20 20 20
g 20 23 21 12 12 12 12 12
h 20 4 4 4 4 50 43 80

Anyone know? I can't figure out how to get match to look at tables rather
than columns.

Thanks a million

Match and Index for Tables
 

You don't need the top table, just the bottom

Assume the bottom table is within N20:V28
Input in M10, eg: ec

Then in say, M9:
=INDEX(O21:V28,MATCH(RIGHT(M10),N21:N28,0),MATCH(L EFT(M10),O20:V20,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"LiAD" wrote:
Can anyone tell me how to get the match and index functions to work for an
8x8 table?

So for example I would like the function to take a given text string ec for
example, search for its position in the first table, then look at the second
table and return the value the corresponds to the text string located in the
first box.

Examples - I ask the function to find the value corresponding to; ec the
function returns the value 99, for bh - 4, for ga- 78.

The text string to match is in cell m10, and the top left corner of the
tables begins in cell N10.

Tables below

a b c d e f g h
a aa ba ca da ea fa ga ha
b ab bb cb db eb fb gb hb
c ac bc cc dc ec fc gc hc
d ad bd cd dd ed fd gd hd
e ae be ce de ee fe ge he
f af bf cf df ef ff gf hf
g ag bg cg dg eg fg gg hg
h ah bh ch dh eh fh gh hh

a b c d e f g h
a 20 11 30 33 44 40 78 54
b 20 20 20 20 20 20 20 20
c 20 43 5 90 99 67 60 56
d 20 20 20 20 20 20 20 20
e 20 20 20 20 20 20 20 20
f 20 20 20 20 20 20 20 20
g 20 23 21 12 12 12 12 12
h 20 4 4 4 4 50 43 80

Anyone know? I can't figure out how to get match to look at tables rather
than columns.

Thanks a million

Match and Index for Tables
 

works well, except for the fact that list i want to match it to is from a
validation list, so its a preselected list which the user selects a b or c
etc. I cannot get match to work unless the value is actually typed into a
cell by itself. Even if i set another cell = to the validation cell its
doesn't work.

Any ideas how to get round this one also?

Thanks
LD

"Max" wrote:

You don't need the top table, just the bottom

Assume the bottom table is within N20:V28
Input in M10, eg: ec

Then in say, M9:
=INDEX(O21:V28,MATCH(RIGHT(M10),N21:N28,0),MATCH(L EFT(M10),O20:V20,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"LiAD" wrote:
Can anyone tell me how to get the match and index functions to work for an
8x8 table?

So for example I would like the function to take a given text string ec for
example, search for its position in the first table, then look at the second
table and return the value the corresponds to the text string located in the
first box.

Examples - I ask the function to find the value corresponding to; ec the
function returns the value 99, for bh - 4, for ga- 78.

The text string to match is in cell m10, and the top left corner of the
tables begins in cell N10.

Tables below

a b c d e f g h
a aa ba ca da ea fa ga ha
b ab bb cb db eb fb gb hb
c ac bc cc dc ec fc gc hc
d ad bd cd dd ed fd gd hd
e ae be ce de ee fe ge he
f af bf cf df ef ff gf hf
g ag bg cg dg eg fg gg hg
h ah bh ch dh eh fh gh hh

a b c d e f g h
a 20 11 30 33 44 40 78 54
b 20 20 20 20 20 20 20 20
c 20 43 5 90 99 67 60 56
d 20 20 20 20 20 20 20 20
e 20 20 20 20 20 20 20 20
f 20 20 20 20 20 20 20 20
g 20 23 21 12 12 12 12 12
h 20 4 4 4 4 50 43 80

Anyone know? I can't figure out how to get match to look at tables rather
than columns.

Thanks a million

Match and Index for Tables
 

Think its much easier to debug the data inconsistencies (that's the problem
you face) by looking at your actual sample ..

Can you upload your sample file using a free filehost,
then post a link to it here?

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"LiAD" wrote:
works well, except for the fact that list i want to match it to is from a
validation list, so its a preselected list which the user selects a b or c
etc. I cannot get match to work unless the value is actually typed into a
cell by itself. Even if i set another cell = to the validation cell its
doesn't work.

Any ideas how to get round this one also?

Thanks
LD

Match and Index for Tables
 

Hi
Max's formula works just fine, and returns the correct result.
If you are saying the user chooses from a dropdown list, which only has
a,b,c etc., to give the column offset, there must be a second choice to
choose the row offset.

Perhaps if the one choice is in say cell N8 and the other in N9, all you
need in cell N10 is
=N8&N9
--
Regards
Roger Govier

"LiAD" wrote in message
...
works well, except for the fact that list i want to match it to is from a
validation list, so its a preselected list which the user selects a b or c
etc. I cannot get match to work unless the value is actually typed into a
cell by itself. Even if i set another cell = to the validation cell its
doesn't work.

Any ideas how to get round this one also?

Thanks
LD

"Max" wrote:

You don't need the top table, just the bottom

Assume the bottom table is within N20:V28
Input in M10, eg: ec

Then in say, M9:
=INDEX(O21:V28,MATCH(RIGHT(M10),N21:N28,0),MATCH(L EFT(M10),O20:V20,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"LiAD" wrote:
Can anyone tell me how to get the match and index functions to work for
an
8x8 table?

So for example I would like the function to take a given text string ec
for
example, search for its position in the first table, then look at the
second
table and return the value the corresponds to the text string located
in the
first box.

Examples - I ask the function to find the value corresponding to; ec
the
function returns the value 99, for bh - 4, for ga- 78.

The text string to match is in cell m10, and the top left corner of the
tables begins in cell N10.

Tables below

a b c d e f g h
a aa ba ca da ea fa ga ha
b ab bb cb db eb fb gb hb
c ac bc cc dc ec fc gc hc
d ad bd cd dd ed fd gd hd
e ae be ce de ee fe ge he
f af bf cf df ef ff gf hf
g ag bg cg dg eg fg gg hg
h ah bh ch dh eh fh gh hh

a b c d e f g h
a 20 11 30 33 44 40 78 54
b 20 20 20 20 20 20 20 20
c 20 43 5 90 99 67 60 56
d 20 20 20 20 20 20 20 20
e 20 20 20 20 20 20 20 20
f 20 20 20 20 20 20 20 20
g 20 23 21 12 12 12 12 12
h 20 4 4 4 4 50 43 80

Anyone know? I can't figure out how to get match to look at tables
rather
than columns.

Thanks a million