Simple VLOOKUP, but not working

Hi, I've made a simple VLOOKUP, and for the majority of cells it comes back with the correct value. However, for some it says N/A. What is weird is that those values I have looked up in the table_ar...

excel

Excel on Mac

Formulas and Functions

mtarler
Sep 30, 2022
EmTBHC The problem is the lookup_range for all those cells keep changing and so some cells are looking up in ranges that don't have that value. I have attached an updated version using dynamic arrays and only 1 array formula for the whole range:
=VLOOKUP(D2:D97,Sheet1!$A$2:$B$391, 2, FALSE)
if you don't have excel 365 then change the D2:D97 to only D2 and then fill down. Since the D2 doesn't have any $ in front it will increment as it fills down but the range has those $ in front of the col and row so it will stay fixed when copied or filled.
This solution was also noted above but you said it didn't work for you but maybe you didn't understand the importance of those $
To "fill down" you can select the top cell and click the small box in the bottom right corner and drag down or select the whole range can click ctrl-D
test2.xlsx37 KB

mtarler

Silver Contributor

Sep 28, 2022

can you include the sheet and formulas. If not attached here then as a link to onedrive or dropbox or the like. It is really hard to guess all the possibilities. It could be formatting or if you defined the lookup to be exact match or not, or numerous other options.

EmTBHC
Copper Contributor
Sep 28, 2022
Hi mtarler Thanks for your quick reply.
The formula I'm using is this =VLOOKUP(D2,Sheet1!A2:B391, 2, FALSE)

I appreciate this might be a silly questions, but I how to I attach a file in this conversation?
- Patrick2788
  Silver Contributor
  Sep 28, 2022
  EmTBHC
  Could be your table array. Try this:
  
  =VLOOKUP(D2,Sheet1!$A$2:$B$391, 2, FALSE)
  - EmTBHC
    Copper Contributor
    Sep 28, 2022
    Hi Patrick, just tried, that sadly didn't work.

Forum Discussion

Simple VLOOKUP, but not working

Resources