ExcelBanter - Read Matrix via function

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Read Matrix via function (https://www.excelbanter.com/excel-programming/422996-read-matrix-via-function.html)

dan

Read Matrix via function

Hi,
I have a matrix as follow

Name/Grade Dan Ben Dave Laura
Math A A+ C A
English B A B A+

How can I have a VBA fuction that will read this matrix, for example

Name_Grade( Ben , English ) = "A"
Name_Grade( Dave , Math ) = "C"

Many thanks,
Dan

Mike H

Read Matrix via function

Dan,

A formula is much simpler

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where F1 is the subject and G1 is the name to lookup

Mike

"Dan" wrote:

Hi,
I have a matrix as follow

Name/Grade Dan Ben Dave Laura
Math A A+ C A
English B A B A+

How can I have a VBA fuction that will read this matrix, for example

Name_Grade( Ben , English ) = "A"
Name_Grade( Dave , Math ) = "C"

Many thanks,
Dan

Pilar Morales

Read Matrix via function

Great formula! Does it work on Pivot tables- when the lookup value is in a
different sheet ? Does it require Ctrl+Enter?
Thanks (sorry to interrupt)

"Mike H" wrote:

Dan,

A formula is much simpler

=INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0))

Where F1 is the subject and G1 is the name to lookup

Mike

"Dan" wrote:

Hi,
I have a matrix as follow

Name/Grade Dan Ben Dave Laura
Math A A+ C A
English B A B A+

How can I have a VBA fuction that will read this matrix, for example

Name_Grade( Ben , English ) = "A"
Name_Grade( Dave , Math ) = "C"

Many thanks,
Dan

All times are GMT +1. The time now is 02:40 PM.