I have two workbook and i managed to perform vlookup on both of them. However, i have issues with the formula not working when new record is added on both workbook. I tried using dynamic name range, offset and it works but my lecturer said that my formula was wrong and told that I only needed vlookup function. I was like surprised so I tried to google search but no dice. Is there a way to make vlookup works when new record is added without using any function other than vlookup? Thx.

Well let's say your Vlookup is =VLOOKUP(A1,$D$1:$E$8,2,0)

If you add a record to row 9, the VLOOKUP would fail because it is not a dynamic range.

So instead, reference the entire column =VLOOKUP(A1,$D:$E,2,0)

It is indeed correct that you only need VLOOKUP ( although the INDEX/MATCH combination is much better IMO) using a dynamic range or a table as data source, so what's wrong with that?
You VLOOKUP would look something like =VLOOKUP("ab";Table1[#All];2;0) with a table

And I also think that using entire columns is not the best way to go as it forbids you to use those columns for other things than extending your data source

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thx man, I followed your advice and I managed to get my formula work with new record inserted. Hopefully my lecturer satisfied with this approach.


I tried many ways on making my vlookup formula dynamically and all of them work but my lecturer rejected them, saying that you only needed vlookup lol. I tried jeffreybrown's solution and it works but don't know whether my lect accept it or not. If not, then I'l use your solution and if he still rejected it then i dont know what else lol

You're very welcome.

I agree, the entire column reference might be overkill, so instead of $D:$E you could extend to a reasonable length which would catch the new adds.

Maybe $D$1:$E$200 would suffice

I'm sure Jeff's solution will be OK'd

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thx guys for all your help. My lecturer finally satisfied with my formula lol.

Great news

What solution did you offer that worked?

I used your suggestion and changed the table array so that it referred to the whole column.

Good job...

For your next step/solution...

http://www.contextures.com/xlNames01.html

Thx, i appreciate your help!