Need help with vlookup

[kimak]

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.

[jeffreybrown]

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)

[Pecoflyer]

Originally Posted by kimak

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.

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

[kimak]

Originally Posted by jeffreybrown

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)

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.

Originally Posted by Pecoflyer

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

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

[jeffreybrown]

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

[Pecoflyer]

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

[kimak]

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

[jeffreybrown]

Great news

What solution did you offer that worked?

[kimak]

Originally Posted by jeffreybrown

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.

[jeffreybrown]

Good job...

For your next step/solution...

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

[kimak]

Originally Posted by jeffreybrown

Good job...

For your next step/solution...

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

Thx, i appreciate your help!