Lookups

  • Thread starter Thread starter patrickbirch
  • Start date Start date
P

patrickbirch

I am trying to write a formula which looks up a product code in a list,
and then references a date to pick out a piece of information.

The code is in a horizontal list with the dates being in a vertical
list across the top of the page.

eg
Code Dates
Jan Feb Mar
111 50 70 80
112 90 10 60
113 60 10 80

It needs to refer to the code then the month, to be able to select the
correct cell.

eg
code 112 in February = 10

Is there a function to cross reference the two pieces of information to
select the data.

Any ideas would be great
 
patrickbirch said:
I am trying to write a formula which looks up a product code in a list,
and then references a date to pick out a piece of information.

The code is in a horizontal list with the dates being in a vertical
list across the top of the page.

eg
Code Dates
Jan Feb Mar
111 50 70 80
112 90 10 60
113 60 10 80

It needs to refer to the code then the month, to be able to select the
correct cell.

eg
code 112 in February = 10

Is there a function to cross reference the two pieces of information to
select the data.

Any ideas would be great
Click to expand...


Your post makes me very confused, I'm afraid! You talk of code being in a
horizontal list and then show it vertically. Then you say: "dates being in a
vertical list across the top of the page".

If it helps, this formula looks up A1(row) and B1 (column) in an array named
'Table':
=INDEX(Table,MATCH(A1,INDEX(Table,,1),0),MATCH(B1,INDEX(Table,1,),0))
 
If you have named ranges, you can simply use =Code*Month, OR in this case
=112*Feb or simply =112single spaceFeb
 
Back
Top