|
CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE?
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(SEARCH RANGE),1,FALSE) Can someone please help??? |
CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE?
Instead of SEARCHRANGE, try INDIRECT(A1) where A1 has the search range in it.
"njuneardave" wrote: 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(SEARCH RANGE),1,FALSE) Can someone please help??? |
CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE?
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: | column B | ----------------------- 1 blah 2 SearchRange 3 SearchRange 4 SearchRange 5 Search Range 6 blah 7 blah 8 blah 9 blah 10 blah TO THIS ONE: | column B | ----------------------- 1 blah 2 blah 3 blah 4 blah 5 blah 6 blah 7 blah 8 SearchRange 9 SearchRange 10 blah 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,FA LSE) "Barb Reinhardt" wrote: Instead of SEARCHRANGE, try INDIRECT(A1) where A1 has the search range in it. "njuneardave" wrote: 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(SEARCH RANGE),1,FALSE) Can someone please help??? |
CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE?
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) "njuneardave" wrote: 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: | column B | ----------------------- 1 blah 2 SearchRange 3 SearchRange 4 SearchRange 5 Search Range 6 blah 7 blah 8 blah 9 blah 10 blah TO THIS ONE: | column B | ----------------------- 1 blah 2 blah 3 blah 4 blah 5 blah 6 blah 7 blah 8 SearchRange 9 SearchRange 10 blah 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,FA LSE) "Barb Reinhardt" wrote: Instead of SEARCHRANGE, try INDIRECT(A1) where A1 has the search range in it. "njuneardave" wrote: 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(SEARCH RANGE),1,FALSE) Can someone please help??? |
| All times are GMT +1. The time now is 02:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com