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

WELL NAME	X	Y	FORMATION	INTERPRETER	MD	TVD
RA-0001	764038.1957	3309483.524	MaA	IRT07	7350	7349.2056
RA-0001	764038.1957	3309483.524	MaA	IRT	7350	7349.2056
RA-0001	764038.1957	3309483.524	MaA_L	IRT07	7362.8599	7362.0645
RA-0001	764038.1957	3309483.524	MaA_L	IRT	7362.8599	7362.0645
RA-0001	764038.1957	3309483.524	MaA_Mrk1	IRT07	7353.4946	7352.6997
RA-0001	764038.1957	3309483.524	MaA_Mrk1	IRT	7353.4946	7352.6997
RA-0001	764038.1957	3309483.524	MaA_Mrk2	IRT07	7364.606	7363.8105
RA-0001	764038.1957	3309483.524	MaA_Mrk2	IRT	7364.606	7363.8105
RA-0001	764038.1957	3309483.524	MaB	IRT07	7366.5708	7365.7749
RA-0001	764038.1957	3309483.524	MaB	IRT	7366.5708	7365.7749
RA-0001	764038.1957	3309483.524	MaB_Mrk1	IRT07	7366.5708	7365.7749
RA-0001	764038.1957	3309483.524	MaB_Mrk1	IRT	7366.5708	7365.7749
RA-0001	764038.1957	3309483.524	MaB_Mrk2	IRT07	7374.2422	7373.4458
RA-0001	764038.1957	3309483.524	MaB_Mrk2	IRT	7374.2422	7373.4458
RA-0001	764038.1957	3309483.524	MaB_Mrk3	IRT07	7380.5039	7379.707
RA-0001	764038.1957	3309483.524	MaB_Mrk3	IRT	7380.5039	7379.707
RA-0001	764038.1957	3309483.524	MaC	IRT07	7388.334	7387.5366
RA-0001	764038.1957	3309483.524	MaC	IRT	7388.334	7387.5366
RA-0001	764038.1957	3309483.524	MaC1	IRT07	7391.7998	7391.002
RA-0001	764038.1957	3309483.524	MaC2	SAA	7394.1201	7393.3223
RA-0001	764038.1957	3309483.524	MaC2	IRT07	7402.2983	7401.5
RA-0001	764038.1957	3309483.524	MaC2	IRT	7402.2983	7401.5
RA-0001	764038.1957	3309483.524	MaC3	IRT07	7409.4907	7408.6914
RA-0001	764038.1957	3309483.524	MaC4	IRT07	7416.0898	7415.29
RA-0001	764038.1957	3309483.524	MaD	IRT07	7415.2422	7414.4424
RA-0001	764038.1957	3309483.524	MaD	IRT	7415.2422	7414.4424
RA-0001	764038.1957	3309483.524	MaD_Mrk1	IRT07	7431.6104	7430.8086
RA-0001	764038.1957	3309483.524	MaD_Mrk1	IRT	7431.6104	7430.8086
RA-0001	764038.1957	3309483.524	MaD_Mrk2	IRT07	7449.7271	7448.9238
RA-0001	764038.1957	3309483.524	MaD_Mrk2	IRT	7449.7271	7448.9238
RA-0001	764038.1957	3309483.524	MaD_Mrk3	IRT07	7464.5518	7463.7466
RA-0001	764038.1957	3309483.524	MaD_Mrk3	IRT	7464.5518	7463.7466
RA-0001	764038.1957	3309483.524	MaD_Mrk4	IRT07	7477.1992	7476.3931
RA-0001	764038.1957	3309483.524	MaD_Mrk4	IRT	7477.1992	7476.3931
RA-0001	764038.1957	3309483.524	MaE	SAA	7481.0942	7480.2876
RA-0001	764038.1957	3309483.524	MaE	IRT07	7481.1128	7480.3062
RA-0001	764038.1957	3309483.524	MaE	IRT	7481.1128	7480.3062
RA-0001	764038.1957	3309483.524	MaE_Mrk1	IRT07	7490.2305	7489.4229
RA-0001	764038.1957	3309483.524	MaE_Mrk1	IRT	7490.2305	7489.4229

<tbody>
</tbody>

 

[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.