INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)

Since the third criterion is 0 it is actually returning an array of the whole first row of the range referenced. The 1 tells the formula that the first row is wanted.

But since the Range is only one row the INDEX is just added noise and can be removed.

Trans_CIQ!$A$886:$AZ$886

will return the same array.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,INDEX(Trans_CIQ!$A$886:$AZ$886,1,0))

Finds the value that is in D9 in the array returned by the Index and returns the relative column number. This column Number being returned to the VLOOKUP dictates which column to return as the answer.

But as stated above the INDEX is not needed.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,Trans_CIQ!$A$886:$AZ$886)

So lets say that the MATCH returns column 5 as where the value in D9 falls in the list contained in Trans_CIQ!$A$886:$AZ$886 It would return 5 to the VLOOKUP's third Criterion.

VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,5,0)

This then finds C10 in Trans_CIQ!$A$8:$A$95 And returns the value from the 5th column, or in this case Column E, of that row in which the value is found.

After that the *100000 multiplies the return by 100000

INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)

Since the third criterion is 0 it is actually returning an array of the whole first row of the range referenced. The 1 tells the formula that the first row is wanted.

But since the Range is only one row the INDEX is just added noise and can be removed.

Trans_CIQ!$A$886:$AZ$886

will return the same array.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,INDEX(Trans_CIQ!$A$886:$AZ$886,1,0))

Finds the value that is in D9 in the array returned by the Index and returns the relative column number. This column Number being returned to the VLOOKUP dictates which column to return as the answer.

But as stated above the INDEX is not needed, Also it being in the Third Criterion it should be returning an error and not working, replace it with -1, 0 or 1.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,0)

So lets say that the MATCH returns column 5 as where the value in D9 falls in the list contained in Trans_CIQ!$A$8:$AZ$8 It would return 5 to the VLOOKUP's third Criterion.

VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,5,0)

This then finds C10 in Trans_CIQ!$A$8:$A$95 And returns the value from the 5th column, or in this case Column E, of that row in which the value is found.

After that the *100000 multiplies the return by 100000

INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)

Since the third criterion is 0 it is actually returning an array of the whole first row of the range referenced. The 1 tells the formula that the first row is wanted.

But since the Range is only one row the INDEX is just added noise and can be removed.

Trans_CIQ!$A$886:$AZ$886

will return the same array.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)),0)

Finds the value that is in D9 in the array returned by the Index and returns the relative column number. This column Number being returned to the VLOOKUP dictates which column to return as the answer.

But as stated above the INDEX is not needed.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,Trans_CIQ!$A$886:$AZ$886)

So lets say that the MATCH returns column 5 as where the value in D9 falls in the list contained in Trans_CIQ!$A$886:$AZ$886 It would return 5 to the VLOOKUP's third Criterion.

VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,5,0)

This then finds C10 in Trans_CIQ!$A$8:$A$95 And returns the value from the 5th column, or in this case Column E, of that row in which the value is found.

After that the *100000 multiplies the return by 100000

INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)

Since the third criterion is 0 it is actually returning an array of the whole first row of the range referenced. The 1 tells the formula that the first row is wanted.

But since the Range is only one row the INDEX is just added noise and can be removed.

Trans_CIQ!$A$886:$AZ$886

will return the same array.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,INDEX(Trans_CIQ!$A$886:$AZ$886,1,0))

Finds the value that is in D9 in the array returned by the Index and returns the relative column number. This column Number being returned to the VLOOKUP dictates which column to return as the answer.

But as stated above the INDEX is not needed.

MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,Trans_CIQ!$A$886:$AZ$886)

So lets say that the MATCH returns column 5 as where the value in D9 falls in the list contained in Trans_CIQ!$A$886:$AZ$886 It would return 5 to the VLOOKUP's third Criterion.

VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,5,0)

This then finds C10 in Trans_CIQ!$A$8:$A$95 And returns the value from the 5th column, or in this case Column E, of that row in which the value is found.

After that the *100000 multiplies the return by 100000