Your situation HAS to involve getting data in which the columns are shifted on you. Perhaps that is a data export from somewhere that has such changes and no way you can influence the creation or form of, or perhaps you are scraping a webpage. Or fifty other ways.
The reason I say this is that INDEX/MATCH (and the more recent XLOOKUP) DO in fact preserve you from this kind of problem vis-a-vis column insertions. Excel adjusts for such things, and does not fail. So the data must be coming in already placed so that it will be in a different column.
No problem, as it can be worked around. The easiest way is the one you ask about: search out the column based upon an unchanging column header. It WILL have to remain the same, of course, or you will need a further tweak or two for ease of use, depending upon the exact situation.
So then, how to do that? You will use match taking the known column header you are seeking and checking against a range that is the row of the headers, let's say row 1, and all the columns in not just the current data range, maybe that's as far over as column AZ at the moment, but farther right of that, let's say column BZ. So the range would be A1:BZ1. You start at column A so that your "MATCH number" (the column the header is in's column number) is easy to use. So if the data range is really C1:AZ263 (headers included), you don't have to get a MATCH number of, say, 1 for column C which is column 1 in the range, then add 2 to get the column 3 that it is in the larger world. Simpler to not have to adjust. So long as your unique column header is not in extra places in row 1, that's the easy way.
If you have the column from MATCH-ing on the column header, then the ADDRESS function will take that and a row number and give you their Excel address. You want to start looking for your lookup value in row 2, so you know it to start and are learning what column to make an address for. Today it turns out to be column AK, so column 37. ADDRESS then takes row 2 and column 37 and spits out the address $AK$2.
You can do that twice, once giving it row 2 and once giving it row 263 to get $AK$2 and $AK$263. If you make those into a string and slip in &":"& between them, you have: $AK$2:$AK$263 . Not a "real boy" just yet, but the INDIRECT function will take a string like that and make it into a real address: INDIRECT($AK$2:$AK$263) is a real address to Excel and returns the column of data into the formula.
The INDEX/MATCH formula you are using is basically two parts. The inner part is the MATCH function which returns the row you need and the INDEX part, the outer part, takes it and finds that row in the column of data it is indexing. In the following formula, the lookup value typed by someone is in cell N3, it is used to look in column AK which is labelled CE Comment to find the index (row, basically) of the lookup value in N3. That index is passed on to the INDEX function at the start of the formula as the desired row to return. The range INDEX is indexing is column C which I've given the label pig since you did not give its name.
So, the logic is:
Enter a lookup value in N3.
Use MATCH (twice) on the pig headered column to find its column number.
Pass that and the starting row number to ADDRESS to get a proper address for the beginning of the lookup value search range.
Pass that and the ending row number to ADDRESS to get a proper address for the end of the lookup value search range.
Slip the colon in between 3 and 4, above.
Wrap all that in INDIRECT to make it a real range to Excel.
Use MATCH and the lookup value on that range to get the index of the matching value.
If happy that column will never need this kind of looking up, then just use its $C$2:$C$263 address directly and skip the above.
That half of an INDEX/MATCH finished, build the range you tell INDEX to find the answer in in exactly the same way, just using the CE Comment column header.
So now, when the column order is shifted on you, you still locate the CE Comment column automatically and always have the right answer.
Formula:
=INDEX(INDIRECT(ADDRESS(2,MATCH("CE Comment",A1:BZ1,0))&":"&ADDRESS(263,MATCH("CE Comment",A1:BZ1,0))),MATCH(N3,INDIRECT(ADDRESS(2,MATCH("pig",A1:BZ1,0))&":"&ADDRESS(263,MATCH("pig",A1:BZ1,0)))))
You need to change the column headers to match your real ones. The 2's in it look like they should remain the same. The 263's can be changed if the table grows downward. Of course, you'd add to the addressing strings to put in the references like '[Supply Master Tracker.xlsx]FY21 Supply Risks'! to properly complete the bare address ADDRESS generates. Like so:
INDIRECT("'[Supply Master Tracker.xlsx]FY21 Supply Risks'!" & ADDRESS(2,MATCH("pig",A1:BZ1,0)) & ":" &...etc.
All just part of building the strings needed.
After you get things going, there are all kinds of ways you can "automate" every last bit of it. Starting and ending rows can be calculated and/or located, and so on. But this will get you on your way.
There are other ways to get the addresses too, and if you find one you prefer, you can just convert to that.
If you have XLOOKUP it becomes easier yet, lots in this case. If you have LET, you can make it all look vastly clearer to anyone looking at the formula.
