Forum Discussion

shetzel's avatar
shetzel
Copper Contributor
May 12, 2023

Auto populate different cells based on one cells value and not have VLOOKUP return #N?A if value not

I am trying to have cells B6, B8 and B10 auto populate from the Data tab based on B4's input value. If the number in B4 matches what is in Data in column A, return the values, but if it is not found, how do I get cells B6, B8 and B10 to show blank instead of the #N?A?

Thanks Scott

excel

7 Replies

Sort By
    • shetzel's avatar
      shetzel
      Copper Contributor
      May 12, 2023
      I forgot to mention that we have some part numbers that start with a zero and if it is not text, it will not find or show the correct part number in cell B4.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        May 12, 2023

        shetzel 

        =IFERROR(IFERROR(VLOOKUP(NUMBERVALUE(B4),Data!A2:F100,2,FALSE),VLOOKUP(B4,Data!A2:F100,2,FALSE)),"")

        This formula seems to work. It checks if the numbervalue of the text in cell B4 is found in the Data sheet, then it checks if the textvalue from cell B4 is found in the Data sheet and otherwise it returns a blank cell.

         

Resources