Vlookup new added items

[peter8848]

Hi,

I have a "main spreadsheet" which vlookups another "spreadsheet data".

However there are always new item lookup value in "spreadsheet data" and I need to add it in "main spreadsheet" a few times due to the "main spreadsheet" has to lookup it differently for different weeks period from the "spreadsheet data"

What is the best method (hopefully some sort of formulas) to add or find out new lookup item values apart from VBA? Or if we have to use VBA how I should design a code?

Cheers,

Peter

 

[Yongle]

Learn about Named Ranges and in particular Dynamic Named Ranges
- they "grow" automatically as new data is added
- see this excellent tutorial or this one

OR (my lazy way)
With Vlookup you can often use whole columns as your lookup range
=VLOOKUP("FindThis",A:B,2,0)
rather than a static range which is very inflexible
=VLOOKUP("FindThis",$A$2:$B$15,2,0)

OR
I like using an Excel table (created with Insert \ Table ) for the lookup range
- tables grow automatically as more entries are added
=VLOOKUP("FindThis",Table1[#All],2,0)

 

[peter8848]

Hi Yongle,

Thanks for your reply.

In fact I am actually referring to the section of "FindThis" rather than the lookup range, is there a way we can do this?

=VLOOKUP(Table1[#],$A$2:$B$15,2,0) - look for the 1st value from the table
=VLOOKUP(Table1[#],$A$2:$B$15,2,0) - look for the 2nd value from the table

...


thanks

 

[Yongle]

I think this is what you need to get the first argument of your VLookup formula

=INDEX(Table1[MyHeader],#)


Example - table name is Table1, column Header MyHeader, with 4 rows as below

MyHeader

a

b

c

d

<tbody>
</tbody>

=INDEX(Table1[MyHeader],1) returns a
=INDEX(Table1[MyHeader],3) returns c

Leading to
=VLOOKUP(INDEX(Table1[MyHeader],2),$A$2:$B$15,2,0)
which looks for a match for b in A2:A15

 

[peter8848]

Hi Yongle,

Thanks,

If the sequence of the table data changes and could we please use index formula to list them out in column in alphabetical order?

 

[Yongle]

Amended VLookup Formula
Add a helper column to Table1 (see below) and point the INDEX formula at that column. Amended formula:

=VLOOKUP(INDEX(Table1[AlphaSort],1),$A$2:$B$15,2,0)

Adding helper column to Table1
- add header AlphaSort in B1
- insert this ARRAY FORMULA in B2 and commit with {CTRL}{SHIFT}{ENTER}

=INDEX([MyHeader],MATCH(ROWS($A$2:A2),COUNTIF([MyHeader],"<="&[MyHeader]),0))

Notes re helper column
Table1 is in column A (starting at A1) - amend the formula if yours differs

Excel auto-wraps { } around an ARRAY FORMULA
- typing them in manually does not work
- the formula will look like this:
{=INDEX([MyHeader],MATCH(ROWS($A$2:A2),COUNTIF([MyHeader],"<="&[MyHeader]),0))}