Lookup function

[mib1019]

Good morning. I'm looking for some help with what I think would be a lookup function.

Here is a sample of what my two tables look like. In the right table, I need to look up the values in the left table with the rows and columns switched. Is there a formula that would work to do this?

Thanks in advance for any help you can give me.

MB

 

[jasonb75]

Using office 365, try entering =TRANSPOSE(Table1[#all]) into a single cell. Change the name of the table to whatever yours is called.

 

[mib1019]

Thanks for your suggestion, but I'll want to maintain a listobject for the transposed cells, which I can't do with the resulting array. I also cannot sort the transposed data by the left column.

Any other ideas?

 

[jasonb75]

In that case, it's a simple INDEX and MATCH combination.

I can't see formatting in a screen capture, the dates in the column headers of the green table will definitely be text. If the dates in the other table are also text then use this formula.

=INDEX(Table1,MATCH([[#Headers],[31-Oct]],Table1[Date],0),MATCH([@Fruit],Table1[#Headers],0))

or if they are proper dates then you need this one.

=INDEX(Table1,MATCH(DATEVALUE([[#Headers],[31-Oct]]),Table1[Date],0),MATCH([@Fruit],Table1[#Headers],0))

 

[mib1019]

Thanks, that worked! Was a little tedious, after copying the formula across the other columns, editing each first row's formula to change the header value[date], but when it's not a big table, it won't be too much trouble.

You rock! Thanks a million.
MB

 

[jasonb75]

Was a little tedious, after copying the formula across the other columns, editing each first row's formula to change the header value[date]

I tested the formula by dragging it right and it updated automatically, the reference is relative when the formula only refers to a single column of the headers.

edit:- Now I realise what you meant, Table1[[Date]:[Date]] would make that part absolute.

 

[mib1019]

So I could have changed it to reference the column instead. Sweet.

Thanks again!

 

[mib1019]

So I could have changed it to reference the column instead. Sweet.

Thanks again!

I changed to a non-table address and it copies just perfectly! Thanks again!

 

[jasonb75]

I changed to a non-table address and it copies just perfectly!

You don't need a non-table address for it to work, it works just as well with the table names if you use the format from my reply.

Table1[Date] is relative Table1[[Date]:[Date]] is absolute.