1 
(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

Improve this question
6
  • You could probably use =(VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,Trans_CIQ!$A$886,0)*100000)
    – PeterH
    Jul 27, 2018 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, 2018 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, 2018 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, 2018 at 14:23
  • And no when using 0 in INDEX it returns the whole row or column @PeterH
    – Scott Craner
    Jul 27, 2018 at 14:24

1 Answer 1

Reset to default
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

Improve this answer
6
  • 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, 2018 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, 2018 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, 2018 at 14:19
  • Yup missed that, not sure why it is in there.
    – Scott Craner
    Jul 27, 2018 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, 2018 at 14:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .