=index(match,match etc. Formula needed.

[srizki]

On one sheet I have wells names in column B, and Column “I” heading “Non Oh”, and column “J” heading, “Overhead.
On another sheet where I need the formula, I have wells names in column B, but each name is twice and column C I have “Non Oh” and “Overhead” for each well name, so on sheet one, I have horizontally and on sheet 2 I have vertically.
[TABLE="width: 677"]
<tbody>[TR]
[TD]WELL
[/TD]
[TD]RC
[/TD]
[TD]int
[/TD]
[TD]Interest %
[/TD]
[TD]TOTAL
[/TD]
[TD]OverHead
[/TD]
[TD]Regular
[/TD]
[TD]Non Oh
[/TD]
[TD]Overhead
[/TD]
[/TR]
[TR]
[TD]MELVIN 1 1
[/TD]
[TD]411042876
[/TD]
[TD]2.03528
[/TD]
[TD]0.0203528
[/TD]
[TD]309.23
[/TD]
[TD]0
[/TD]
[TD]309.23
[/TD]
[TD]6.29
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]MCMAKIN 1 1
[/TD]
[TD]411042894
[/TD]
[TD]0.50882
[/TD]
[TD]0.0050882
[/TD]
[TD]10574.54
[/TD]
[TD]1844.66
[/TD]
[TD]8729.88
[/TD]
[TD]44.42
[/TD]
[TD]9.39
[/TD]
[/TR]
[TR]
[TD]CHURCH 1 1
[/TD]
[TD]411043528
[/TD]
[TD]1.59915
[/TD]
[TD]0.0159915
[/TD]
[TD]2403.89
[/TD]
[TD]1844.66
[/TD]
[TD]559.23
[/TD]
[TD]8.94
[/TD]
[TD]29.5
[/TD]
[/TR]
[TR]
[TD]BLUNDELL 1 1
[/TD]
[TD]411043413
[/TD]
[TD]1.59915
[/TD]
[TD]0.0159915
[/TD]
[TD]2411.01
[/TD]
[TD]1844.66
[/TD]
[TD]566.35
[/TD]
[TD]9.06
[/TD]
[TD]29.5
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 592"]
<tbody>[TR]
[TD]MELVIN 1 1
[/TD]
[TD]Non Oh
[/TD]
[TD]411042876
[/TD]
[TD]88001610
[/TD]
[TD]2.03528
[/TD]
[TD]0.020353
[/TD]
[TD]WELL
[/TD]
[/TR]
[TR]
[TD]MELVIN 1 1
[/TD]
[TD]Revenue
[/TD]
[TD]411042876
[/TD]
[TD]13500100
[/TD]
[TD]2.03528
[/TD]
[TD]0.020353
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]MCMAKIN 1 1
[/TD]
[TD]Non Oh
[/TD]
[TD]411042894
[/TD]
[TD]88001610
[/TD]
[TD]0.50882
[/TD]
[TD]0.005088
[/TD]
[TD]MELVIN 1 1
[/TD]
[/TR]
[TR]
[TD]MCMAKIN 1 1
[/TD]
[TD]Overhead
[/TD]
[TD]411042894
[/TD]
[TD]88300110
[/TD]
[TD]0.50882
[/TD]
[TD]0.005088
[/TD]
[TD]411042876
[/TD]
[/TR]
[TR]
[TD]CHURCH 1 1
[/TD]
[TD]Non Oh
[/TD]
[TD]411043528
[/TD]
[TD]88001610
[/TD]
[TD]1.59915
[/TD]
[TD]0.015992
[/TD]
[TD]MCMAKIN 1 1
[/TD]
[/TR]
[TR]
[TD]CHURCH 1 1
[/TD]
[TD]Overhead
[/TD]
[TD]411043528
[/TD]
[TD]88300110
[/TD]
[TD]1.59915
[/TD]
[TD]0.015992
[/TD]
[TD]411042894
[/TD]
[/TR]
[TR]
[TD]BLUNDELL 1 1
[/TD]
[TD]Non Oh
[/TD]
[TD]411043413
[/TD]
[TD]88001610
[/TD]
[TD]1.59915
[/TD]
[TD]0.015992
[/TD]
[TD]CHURCH 1 1
[/TD]
[/TR]
[TR]
[TD]BLUNDELL 1 1
[/TD]
[TD]Overhead
[/TD]
[TD]411043413
[/TD]
[TD]88300110
[/TD]
[TD]1.59915
[/TD]
[TD]0.015992
[/TD]
[TD]411043528
[/TD]
[/TR]
</tbody>[/TABLE]

I want a formula that fill the column Well on sheet two based on sheet one.
So, the formula will see, if it is overhead of non overhead and populate the corresponding value.
I tried unsuccessfully, INDEX , MATCH, MATCH .
Thanks

 

[C with no eyes]

Is the second table showing the results that you want it to display?

 

[srizki]

The last column of second table suppose to populate the values if overhead of non overhead, but as you see it is not showing the correct values.

 

[C with no eyes]

OK.

Could you please type the results that should be in the final column?

 

[srizki]

[TABLE="width: 595"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]RC[/TD]
[TD]ACCT[/TD]
[TD][/TD]
[TD][/TD]
[TD]DESIRES RESULT[/TD]
[/TR]
[TR]
[TD]MELVIN 1 1[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411042876[/TD]
[TD]88001610[/TD]
[TD="align: right"]2.03528[/TD]
[TD="align: right"]0.020353[/TD]
[TD="align: right"]5.32[/TD]
[/TR]
[TR]
[TD]MELVIN 1 1[/TD]
[TD]Revenue[/TD]
[TD="align: right"]411042876[/TD]
[TD]13500100[/TD]
[TD="align: right"]2.03528[/TD]
[TD="align: right"]0.020353[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]MCMAKIN 1 1[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411042894[/TD]
[TD]88001610[/TD]
[TD="align: right"]0.50882[/TD]
[TD="align: right"]0.005088[/TD]
[TD="align: right"]13.31[/TD]
[/TR]
[TR]
[TD]MCMAKIN 1 1[/TD]
[TD]OH[/TD]
[TD="align: right"]411042894[/TD]
[TD]88300110[/TD]
[TD="align: right"]0.50882[/TD]
[TD="align: right"]0.005088[/TD]
[TD="align: right"]9.39[/TD]
[/TR]
[TR]
[TD]CHURCH 1 1[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411043528[/TD]
[TD]88001610[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.015992[/TD]
[TD="align: right"]8.18[/TD]
[/TR]
[TR]
[TD]CHURCH 1 1[/TD]
[TD]OH[/TD]
[TD="align: right"]411043528[/TD]
[TD]88300110[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.015992[/TD]
[TD="align: right"]29.5[/TD]
[/TR]
[TR]
[TD]BLUNDELL 1 1[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411043413[/TD]
[TD]88001610[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.015992[/TD]
[TD="align: right"]11.61[/TD]
[/TR]
[TR]
[TD]BLUNDELL 1 1[/TD]
[TD]OH[/TD]
[TD="align: right"]411043413[/TD]
[TD]88300110[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.015992[/TD]
[TD="align: right"]29.5[/TD]
[/TR]
[TR]
[TD]TRISSELL 1 7[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411042578[/TD]
[TD]88001610[/TD]
[TD="align: right"]1.62267[/TD]
[TD="align: right"]0.016227[/TD]
[TD="align: right"]25.2[/TD]
[/TR]
[TR]
[TD]TRISSELL 1 7[/TD]
[TD]OH[/TD]
[TD="align: right"]411042578[/TD]
[TD]88300110[/TD]
[TD="align: right"]1.62267[/TD]
[TD="align: right"]0.016227[/TD]
[TD="align: right"]29.93[/TD]
[/TR]
[TR]
[TD]CHURCH 2 1[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411043527[/TD]
[TD]88001610[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.015992[/TD]
[TD="align: right"]8.18[/TD]
[/TR]
[TR]
[TD]CHURCH 2 1[/TD]
[TD]OH[/TD]
[TD="align: right"]411043527[/TD]
[TD]88300110[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.015992[/TD]
[TD="align: right"]29.5[/TD]
[/TR]
[TR]
[TD]BLACK 1 7[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411043425[/TD]
[TD]88001610[/TD]
[TD="align: right"]1.62267[/TD]
[TD="align: right"]0.016227[/TD]
[TD="align: right"]9.96[/TD]
[/TR]
[TR]
[TD]BLACK 1 7[/TD]
[TD]OH[/TD]
[TD="align: right"]411043425[/TD]
[TD]88300110[/TD]
[TD="align: right"]1.62267[/TD]
[TD="align: right"]0.016227[/TD]
[TD="align: right"]29.93[/TD]
[/TR]
[TR]
[TD]KAY 2 11[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411042991[/TD]
[TD]88001610[/TD]
[TD="align: right"]0.52931[/TD]
[TD="align: right"]0.005293[/TD]
[TD="align: right"]2.71[/TD]
[/TR]
[TR]
[TD]KAY 2 11[/TD]
[TD]OH[/TD]
[TD="align: right"]411042991[/TD]
[TD]88300110[/TD]
[TD="align: right"]0.52931[/TD]
[TD="align: right"]0.005293[/TD]
[TD="align: right"]7.59[/TD]
[/TR]
[TR]
[TD]LORENZ 7 7[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411042922[/TD]
[TD]88001610[/TD]
[TD="align: right"]1.6075[/TD]
[TD="align: right"]0.016075[/TD]
[TD="align: right"]10.63[/TD]
[/TR]
[TR]
[TD]LORENZ 7 7[/TD]
[TD]OH[/TD]
[TD="align: right"]411042922[/TD]
[TD]88300110[/TD]
[TD="align: right"]1.6075[/TD]
[TD="align: right"]0.016075[/TD]
[TD="align: right"]29.65[/TD]
[/TR]
[TR]
[TD]MCMAKIN 2 1[/TD]
[TD]Non Oh[/TD]
[TD="align: right"]411042893[/TD]
[TD]88001610[/TD]
[TD="align: right"]0.50882[/TD]
[TD="align: right"]0.005088[/TD]
[TD="align: right"]2.6[/TD]
[/TR]
[TR]
[TD]MCMAKIN 2 1[/TD]
[TD]OH[/TD]
[TD="align: right"]411042893[/TD]
[TD]88300110[/TD]
[TD="align: right"]0.50882[/TD]
[TD="align: right"]0.005088[/TD]
[TD="align: right"]9.39


The bold letter values are hard coded, the other are using the following array formula.
=INDEX('Total (2)'!$B$2:$J$14,MATCH(Wells!B3,'Total (2)'!$B$2:$B$14,0),MATCH($H$1:$H$2,'Total (2)'!$B$2:$I$2,0))

FROM THE FOLLWING TABLE
[TABLE="width: 694"]
<tbody>[TR]
[TD]WELL[/TD]
[TD]RC[/TD]
[TD]int[/TD]
[TD]Interest %[/TD]
[TD]TOTAL[/TD]
[TD]Over/Head[/TD]
[TD]Regular[/TD]
[TD]Non Oh[/TD]
[TD]OH[/TD]
[/TR]
[TR]
[TD]MELVIN 1 1[/TD]
[TD]411042876[/TD]
[TD="align: right"]2.03528[/TD]
[TD="align: right"]0.0203528[/TD]
[TD="align: right"]261.37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]261.37[/TD]
[TD="align: right"]5.32[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]MCMAKIN 1 1[/TD]
[TD="align: right"]411042894[/TD]
[TD="align: right"]0.50882[/TD]
[TD="align: right"]0.0050882[/TD]
[TD="align: right"]4460.21[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]2615.55[/TD]
[TD="align: right"]13.31[/TD]
[TD="align: right"]9.39[/TD]
[/TR]
[TR]
[TD]CHURCH 1 1[/TD]
[TD="align: right"]411043528[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.0159915[/TD]
[TD="align: right"]2356.03[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]511.37[/TD]
[TD="align: right"]8.18[/TD]
[TD="align: right"]29.5[/TD]
[/TR]
[TR]
[TD]BLUNDELL 1 1[/TD]
[TD="align: right"]411043413[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.0159915[/TD]
[TD="align: right"]2570.53[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]725.87[/TD]
[TD="align: right"]11.61[/TD]
[TD="align: right"]29.5[/TD]
[/TR]
[TR]
[TD]TRISSELL 1 7[/TD]
[TD="align: right"]411042578[/TD]
[TD="align: right"]1.62267[/TD]
[TD="align: right"]0.0162267[/TD]
[TD="align: right"]3397.51[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]1552.85[/TD]
[TD="align: right"]25.2[/TD]
[TD="align: right"]29.93[/TD]
[/TR]
[TR]
[TD]CHURCH 2 1[/TD]
[TD="align: right"]411043527[/TD]
[TD="align: right"]1.59915[/TD]
[TD="align: right"]0.0159915[/TD]
[TD="align: right"]2356.03[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]511.37[/TD]
[TD="align: right"]8.18[/TD]
[TD="align: right"]29.5[/TD]
[/TR]
[TR]
[TD]BLACK 1 7[/TD]
[TD="align: right"]411043425[/TD]
[TD="align: right"]1.62267[/TD]
[TD="align: right"]0.0162267[/TD]
[TD="align: right"]2458.72[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]614.06[/TD]
[TD="align: right"]9.96[/TD]
[TD="align: right"]29.93[/TD]
[/TR]
[TR]
[TD]KAY 2 11[/TD]
[TD="align: right"]411042991[/TD]
[TD="align: right"]0.52931[/TD]
[TD="align: right"]0.0052931[/TD]
[TD="align: right"]1946.1[/TD]
[TD="align: right"]1434.73[/TD]
[TD="align: right"]511.37[/TD]
[TD="align: right"]2.71[/TD]
[TD="align: right"]7.59[/TD]
[/TR]
[TR]
[TD]LORENZ 7 7[/TD]
[TD="align: right"]411042922[/TD]
[TD="align: right"]1.6075[/TD]
[TD="align: right"]0.016075[/TD]
[TD="align: right"]2506.03[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]661.37[/TD]
[TD="align: right"]10.63[/TD]
[TD="align: right"]29.65[/TD]
[/TR]
[TR]
[TD]MCMAKIN 2 1[/TD]
[TD="align: right"]411042893[/TD]
[TD="align: right"]0.50882[/TD]
[TD="align: right"]0.0050882[/TD]
[TD="align: right"]2356.03[/TD]
[TD="align: right"]1844.66[/TD]
[TD="align: right"]511.37[/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]9.39[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col span="3"><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"><col></colgroup>[/TABLE]

 

[C with no eyes]

Sheet2!H2 =INDEX(Sheet1!A:J,MATCH(B2,Sheet1!B:B,0),MATCH(C2,Sheet1!$1:$1,0))

 

[srizki]

That is the copy of my formula.

 

[steve the fish]

If i copy then paste both tables from post number 1 into cell A1 id use this in cell H7:

=INDEX($A$1:$I$5,MATCH($A7,$A$1:$A$5,0),MATCH($B7,$A$1:$I$1,0))

Its possible to use a VLOOKUP too:

=VLOOKUP(A7,$A$1:$I$5,MATCH(B7,$A$1:$I$1,0),0)

 

[srizki]

I got it.
[TABLE="width: 64"]

<colgroup><col width="64" style="width: 48pt;">
<tbody>[TR]


[TD="width: 64, bgcolor: transparent"]=INDEX(table,MATCH(G3,INDEX(table,,1),0),MATCH(H3,INDEX(table,1,),0))[/TD]


[/TR]

</tbody>[/TABLE]