INDEX function or VLOOKUP

[Brandon21]

I have a spreadsheet of geologic rock formation interpreters. There are about 500 wells and each well has about 40 or so formations. Each of these formations has been picked out by an interpreter of varying skill. I need to create a spreadsheet which has picked out only the best interpreter of each formation for each well.


I believe I could use an INDEX or VLOOKUP function to do this.

I would like to write an equation that can:

Go through each individual well, RA-0001 through RA-0500,

Then pick out one interpreter for each well formation for each individual well. Some formations have multiple interpreters some have only one. I need to pick just one in order, First look for IRT, if IRT is there use that interpreter, if not then IRT07, then SAA, the CPR, then HBH, then SAZ.

Pick IRT first, then IRT07, then SAA, then CPR, then HBH, then SAZ

Is there some equation I can write for this? I have to do this same thing to a bunch of spreadsheets so this would make life much easier.

Hope that is not too confusing.

Any help on this would be great! thanks


A B C D E F G
[TABLE="width: 821"]
<tbody>[TR]
[TD]WELL NAME
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]FORMATION
[/TD]
[TD]INTERPRETER
[/TD]
[TD]MD
[/TD]
[TD]TVD
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA
[/TD]
[TD]IRT07
[/TD]
[TD]7350
[/TD]
[TD]7349.2056
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA
[/TD]
[TD]IRT
[/TD]
[TD]7350
[/TD]
[TD]7349.2056
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA_L
[/TD]
[TD]IRT07
[/TD]
[TD]7362.8599
[/TD]
[TD]7362.0645
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA_L
[/TD]
[TD]IRT
[/TD]
[TD]7362.8599
[/TD]
[TD]7362.0645
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA_Mrk1
[/TD]
[TD]IRT07
[/TD]
[TD]7353.4946
[/TD]
[TD]7352.6997
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA_Mrk1
[/TD]
[TD]IRT
[/TD]
[TD]7353.4946
[/TD]
[TD]7352.6997
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA_Mrk2
[/TD]
[TD]IRT07
[/TD]
[TD]7364.606
[/TD]
[TD]7363.8105
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaA_Mrk2
[/TD]
[TD]IRT
[/TD]
[TD]7364.606
[/TD]
[TD]7363.8105
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB
[/TD]
[TD]IRT07
[/TD]
[TD]7366.5708
[/TD]
[TD]7365.7749
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB
[/TD]
[TD]IRT
[/TD]
[TD]7366.5708
[/TD]
[TD]7365.7749
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB_Mrk1
[/TD]
[TD]IRT07
[/TD]
[TD]7366.5708
[/TD]
[TD]7365.7749
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB_Mrk1
[/TD]
[TD]IRT
[/TD]
[TD]7366.5708
[/TD]
[TD]7365.7749
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB_Mrk2
[/TD]
[TD]IRT07
[/TD]
[TD]7374.2422
[/TD]
[TD]7373.4458
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB_Mrk2
[/TD]
[TD]IRT
[/TD]
[TD]7374.2422
[/TD]
[TD]7373.4458
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB_Mrk3
[/TD]
[TD]IRT07
[/TD]
[TD]7380.5039
[/TD]
[TD]7379.707
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaB_Mrk3
[/TD]
[TD]IRT
[/TD]
[TD]7380.5039
[/TD]
[TD]7379.707
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC
[/TD]
[TD]IRT07
[/TD]
[TD]7388.334
[/TD]
[TD]7387.5366
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC
[/TD]
[TD]IRT
[/TD]
[TD]7388.334
[/TD]
[TD]7387.5366
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC1
[/TD]
[TD]IRT07
[/TD]
[TD]7391.7998
[/TD]
[TD]7391.002
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC2
[/TD]
[TD]SAA
[/TD]
[TD]7394.1201
[/TD]
[TD]7393.3223
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC2
[/TD]
[TD]IRT07
[/TD]
[TD]7402.2983
[/TD]
[TD]7401.5
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC2
[/TD]
[TD]IRT
[/TD]
[TD]7402.2983
[/TD]
[TD]7401.5
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC3
[/TD]
[TD]IRT07
[/TD]
[TD]7409.4907
[/TD]
[TD]7408.6914
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaC4
[/TD]
[TD]IRT07
[/TD]
[TD]7416.0898
[/TD]
[TD]7415.29
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD
[/TD]
[TD]IRT07
[/TD]
[TD]7415.2422
[/TD]
[TD]7414.4424
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD
[/TD]
[TD]IRT
[/TD]
[TD]7415.2422
[/TD]
[TD]7414.4424
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk1
[/TD]
[TD]IRT07
[/TD]
[TD]7431.6104
[/TD]
[TD]7430.8086
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk1
[/TD]
[TD]IRT
[/TD]
[TD]7431.6104
[/TD]
[TD]7430.8086
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk2
[/TD]
[TD]IRT07
[/TD]
[TD]7449.7271
[/TD]
[TD]7448.9238
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk2
[/TD]
[TD]IRT
[/TD]
[TD]7449.7271
[/TD]
[TD]7448.9238
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk3
[/TD]
[TD]IRT07
[/TD]
[TD]7464.5518
[/TD]
[TD]7463.7466
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk3
[/TD]
[TD]IRT
[/TD]
[TD]7464.5518
[/TD]
[TD]7463.7466
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk4
[/TD]
[TD]IRT07
[/TD]
[TD]7477.1992
[/TD]
[TD]7476.3931
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaD_Mrk4
[/TD]
[TD]IRT
[/TD]
[TD]7477.1992
[/TD]
[TD]7476.3931
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaE
[/TD]
[TD]SAA
[/TD]
[TD]7481.0942
[/TD]
[TD]7480.2876
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaE
[/TD]
[TD]IRT07
[/TD]
[TD]7481.1128
[/TD]
[TD]7480.3062
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaE
[/TD]
[TD]IRT
[/TD]
[TD]7481.1128
[/TD]
[TD]7480.3062
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaE_Mrk1
[/TD]
[TD]IRT07
[/TD]
[TD]7490.2305
[/TD]
[TD]7489.4229
[/TD]
[/TR]
[TR]
[TD]RA-0001
[/TD]
[TD]764038.1957
[/TD]
[TD]3309483.524
[/TD]
[TD]MaE_Mrk1
[/TD]
[TD]IRT
[/TD]
[TD]7490.2305
[/TD]
[TD]7489.4229
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Brandon21]

I have edited the sheet as to make it easier maybe. I changed the interpreters names to 1 through 6. 1 being the first one I would like to choose and 6 being the last.

What I am saying is, if interpreter 1 has picked a specific formation of a specific well I do not want any other interpreters formation pick. just interpreter 1's. If interpreter 1 did not pick the formation, I need 2's and so on.

I think this is possible to do in excel. I just have no clue how.