How do you vlookup two columns in a nested array?

0
votes

I am trying to do a vlookup for two columns in another tab and return the results of the source tab. This function is meant to do the following:

  1. Is the cell in column A of the "Aggregate" tab blank

  2. If so, add a "0" in column F of the "Aggregate" tab

  3. If not, check cell in column A and B of the "Aggregate" tab and see if this combination exists in the datasource "BrightEdge" tab column A and B

  4. If the contents of column A and B in the "Aggregate" tab do not exist in the "BrightEdge" tab column A and B add a "0" to column F of the "Aggregate" tab

  5. Otherwise, collect the Min value in the "BrightEdge" tab under column C and add it to the "Aggregate" tab column F

    ={"Rankings";ArrayFormula(IF(A2:A="","",VLOOKUP(IFNA(REGEXEXTRACT(A2:A,"[^/]+//(.+)"), "/"),BrightEdge!$A$8:$F,3,FALSE)))}

This formula currently does not check to make sure both column A and B match to the "BrightEdge" tab column A and B. It only references column A

Please Advise

Here is a link to the working Google Sheet: https://docs.google.com/spreadsheets/d/1iHkU-rNtNhoOKvW_CWY7WU5OLsMFVqEFNRZlx_R-7RY/edit#gid=0

1
google-sheets

1 Answers

1
votes

Try:

={"Rankings";ArrayFormula(IF(A2:A="","",iferror(VLOOKUP(IFNA(REGEXEXTRACT(A2:A,"[^/]+//(.+)"), "/")&"|"&B2:B,{BrightEdge!A:A&"|"&BrightEdge!B:B,BrightEdge!C:C},2,FALSE),)))}