0
(VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)),0)*100000)

This formula works for some reason but I don't understand how. I tried to follow it by separating the functions but still don't understand.

Vlookup(lookup value (company name), table array (where to look), col_index_num (which column to look in), [range lookup] (always 0)) gives value that matches conditions

Match(lookup_value (CapIQ chart title),lookup_array (where to look), [match type] (exact)) gives item number in a list

Index(array (the table), row_num, [column num]) returns the value in the cell

  • You could probably use =(VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,Trans_CIQ!$A$886,0)*100000) – PeterH Jul 27 '18 at 14:08
  • @PeterH not and maintain the functionality of the formula. INDEX(Trans_CIQ!$A$886:$AZ$886,1,0) is actually return a 1 dimensional array of 52 values, to the match to search. – Scott Craner Jul 27 '18 at 14:16
  • @Scott where it indexes as Row 1 & Column 0, will that not always be the same Cell $A$886 ? – PeterH Jul 27 '18 at 14:21
  • Yup skipped right over the second criterion. It should just get rid of the INDEX PART but leave the rest of the MATCH replacing the INDEX with either -1,0,1 @PeterH – Scott Craner Jul 27 '18 at 14:23
  • And no when using 0 in INDEX it returns the whole row or column @PeterH – Scott Craner Jul 27 '18 at 14:24
2

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

  • Thank you Scott! One more follow up question, why is the INDEX/Array in in the match_type spot, I thought that was only supposed to be a -1,0, or 1? – Dmitriy Jul 27 '18 at 14:15
  • It is not, it is in the second spot which is where the range to be searched goes. The third criterion is being left blank, which is fine as it is optional. – Scott Craner Jul 27 '18 at 14:16
  • 1
    MATCH(lookup_value,lookup_array,match_type) so for the lookup value it is D$9, for the lookup_array it is Trans_CIQ!$A$8:$AZ$8, and for the match_type it is Trans_CIQ!$A$886:$AZ$886 from my understanding, or is this incorrect? – Dmitriy Jul 27 '18 at 14:19
  • Yup missed that, not sure why it is in there. – Scott Craner Jul 27 '18 at 14:21
  • Is your data sorted and you want a relative match or an exact match, just replace the whole INDEX with -1,0,1. I will update – Scott Craner Jul 27 '18 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.