I am trying to run a vlookup on visible cells and it is not finding/returning any values. I found the formula online and tried to adapt it to my situation. Currently, I want to return the value in column C. The cell formula is currently:
{=VLOOKUP(A2,IF(SUBTOTAL(3,OFFSET([File2.xlsx]Sheet1!$B:$C,ROW([File2.xlsx]Sheet1!$B:$C)-ROW([File2.xlsx]Sheet1!$B$2),0,1)),[File2xlsx]Sheet1!$B:$C),3,0)}
The file with the vlookup formula contains a 5-character zip code in column A. The filtered file (File2) contains a 5-character zip in column B and program codes in column C. I used crtl+shift+enter to create the array brackets that surround the formula. When I copy the formula down, all returned values are #N/A.
I know the zip fields have matching formats as I tested a regular vlookup of zips and they were found. I can manually find some zips codes in the visible rows, so they can’t all be #N/A. I hope you can help me fix the formula.
{=VLOOKUP(A2,IF(SUBTOTAL(3,OFFSET([File2.xlsx]Sheet1!$B:$C,ROW([File2.xlsx]Sheet1!$B:$C)-ROW([File2.xlsx]Sheet1!$B$2),0,1)),[File2xlsx]Sheet1!$B:$C),3,0)}
The file with the vlookup formula contains a 5-character zip code in column A. The filtered file (File2) contains a 5-character zip in column B and program codes in column C. I used crtl+shift+enter to create the array brackets that surround the formula. When I copy the formula down, all returned values are #N/A.
I know the zip fields have matching formats as I tested a regular vlookup of zips and they were found. I can manually find some zips codes in the visible rows, so they can’t all be #N/A. I hope you can help me fix the formula.