Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-2024-08-06· OpenAIAi2 WildChat-4.8M (AI2)

can you tell me what this does =IF(OR(ISNA(VLOOKUP(C10,'Data…

  1. User

    can you tell me what this does =IF(OR(ISNA(VLOOKUP(C10,'Data CdC'!JJ6:KK12,2,0)),ISNA(HLOOKUP(B10,'Data CdC'!LL4:PP5,2,0))),"",VLOOKUP(C10,'Data CdC'!JJ6:PP12,2+HLOOKUP(B10,'Data CdC'!LL4:PP5,2,0),0))

  2. Assistant

    Certainly! This is an Excel formula that utilizes the IF, OR, ISNA, VLOOKUP, and HLOOKUP functions to perform a lookup operation based on certain conditions. Let's break it down:

    1. VLOOKUP(C10,'Data CdC'!$J$6:$K$12,2,0):

      • This part performs a VLOOKUP using the value in cell C10.
      • It looks in the range 'Data CdC'!$J$6:$K$12.
      • It returns the value in the second column of this range.
      • The 0 at the end indicates an exact match is required.
    2. HLOOKUP(B10,'Data CdC'!$L$4:$P$5,2,0):

      • This part performs an HLOOKUP using the value in cell B10.
      • It looks in the range 'Data CdC'!$L$4:$P$5.
      • It returns the value in the second row of this range.
      • The 0 at the end indicates an exact match is required.
    3. ISNA(...):

      • The ISNA function checks if the result of VLOOKUP or HLOOKUP is #N/A, which means the lookup value wasn't found.
    4. OR(ISNA(...), ISNA(...)):

      • This condition returns TRUE if either the result of the VLOOKUP or the HLOOKUP is #N/A.
    5. IF(..., "", ...):

      • If the OR condition is TRUE (either lookup results in #N/A), the formula returns an empty string "".
      • Otherwise, it performs another VLOOKUP using the result of the original lookups.
    6. VLOOKUP(C10,'Data CdC'!$J$6:$P$12,2+HLOOKUP(B10,'Data CdC'!$L$4:$P$5,2,0),0):

      • If both lookups do not result in #N/A, this part performs a VLOOKUP again on C10.
      • It searches in the expanded range 'Data CdC'!$J$6:$P$12.
      • It attempts to retrieve the (2 + result of HLOOKUP) column from the range.
      • Again, the 0 indicates an exact match is required.

    Summary:

    • The formula checks if both C10 and B10 can successfully find corresponding values using VLOOKUP and HLOOKUP.
    • If either lookup fails with #N/A, the formula returns an empty string.
    • If both lookups succeed, it uses the result of HLOOKUP to perform a final VLOOKUP to get the desired result from an expanded range.