CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE?

[Guest]

I need to search a set of cells:

VLOOKUP(ATable!B2,BTable!$B$2:$B$100,1,FALSE)


but, what if what i need to search changes its location every
time....sometimes its at 2-900.....sometimes its at 5-8......sometimes its at
34-78.....other times its 73-74. Say the name of inside those cells is
SearchRange. so....if you are looking at cells 1-10 in an example u may see
this:

| column B |
-----------------------
1 doesntMatter
2 SearchRange
3 SearchRange
4 SearchRange
5 Search Range
6 doesntMatter
7 doesntMatter
8 doesntMatter
9 doesntMatter
10 doesntMatter

I want to search thru the search range.... in this example it just so
happens to be numbers 2-5. but next time, like i said, it may be at 8-55. I
don't know....so is there a way to do VLOOKUP by reference?

Like:

VLOOKUP(ATable!B2,BTable!$B(SEARCHRANGE):$B(SEARCHRANGE),1,FALSE)


Can someone please help???

 

[Guest]

Instead of SEARCHRANGE, try INDIRECT(A1) where A1 has the search range in it.

 

[Guest]

I am not sure that I understand. What does the INDIRECT function accomplish
that would help if the location of the search range changes from:

TO THIS ONE:


Are you saying to make a cell that will never change....like E73. and put
the name "SearchRange" inside of E73 and then call:

VLOOKUP(ATable!B2,BTable!INDIRECT(SearchRange)1,FALSE)

 

[Guest]

Let's say you have

A5: Sheet2!A$1:E$14
B5: =VLOOKUP(B1,INDIRECT(A5),4,FALSE)
That's the same as having
=VLOOKUP(B1,Sheet2!A$1:E$14,4,FALSE)