I suspect I need to use a different function but I am trying to lookup data and in one tab the city name is in one column, but the tab with the data the city names are spread out over manmy columns.
<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
I am trying this formula to no success:
=INDEX(Sample!$C$4:$I$5,MATCH(A2,Sample!$C$2:$I$2,0),MATCH(C$1,Sample!$B$4:$B$5,0))
Can someone help
Location | Revenue | Expenses | |
Boston | 150000 | 168000 | |
Chicago | 99000 | 107910 | |
Dallas | #REF! | #REF! | |
Denver | #REF! | #REF! | |
Houston | #REF! | #REF! | |
Nw York | #REF! | #REF! | |
San Francisco | #REF! | #REF! |
<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
Boston | Chicago | Dallas | Denver | Houston | Nw York | San Francisco | |
Revenue | 150,000 | 168,000 | 188,160 | 210,739 | 236,028 | 264,351 | 296,073 |
Expenses | 99,000 | 107,910 | 117,622 | 128,208 | 139,747 | 152,324 | 166,033 |
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
I am trying this formula to no success:
=INDEX(Sample!$C$4:$I$5,MATCH(A2,Sample!$C$2:$I$2,0),MATCH(C$1,Sample!$B$4:$B$5,0))
Can someone help