| View previous topic :: View next topic |
| Author |
Message |
Todd Freyburger
Newbie
Joined: 17 Aug 2012
Posts: 4
|
 Posted: Fri Aug 17, 2012 2:37 pm Post subject: Possable idea for vlookup using 2 colums of data input |
 |
|
The Problem: I am trying to use 2 columns of data to find a value. This is for a game. The first data is a character level and the second is his raw honor score and I am trying to Honor Category for that level and raw score. The Window changes for each level.
Possible solution: multiple look up charts on hidden sheets and a nested pair vlookup functions. It will look like something like: simplifying with Column A and Column B as stand in for my data.
Base Function: vlookup(A2;'lookup base':A2:B20,2)
This is the base function and will call up the name of the sheet for the second coulm of data to do its vlookup.
Full Function: vlookup(B2,'vlookup(A2;'lookup base',2)'A2:B7,2)
If this works then I will have to create 21 vlookup sheets.
Can anyone think of a different easier method? |
|
| Back to top |
|
 |
Todd Freyburger
Newbie
Joined: 17 Aug 2012
Posts: 4
|
| Posted: Fri Aug 17, 2012 3:26 pm Post subject: |
|
|
Does not like this anyone have another idea.
It will not read the base Vlookup as a function to find text to put in place. |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1875
Location: Sydney, Australia
|
| Posted: Fri Aug 17, 2012 5:14 pm Post subject: |
|
|
Your Full Function's second parameter must be a range.
You could use the INDIRECT function to convert a string that represents that range into an actual range. Maybe something like...
| Code: | | =VLOOKUP(B2;INDIRECT(VLOOKUP(A2;Address of a lookup table who's 2nd column contains sheet names;2;0)&".A2:B7");2;0) |
The inside VLOOKUP looks for the A2 value in the first column of the lookup table referred to then returns the corresponding value in that table's 2nd column, which I have assumed is a sheet name string. The INDIRECT function concatenates this with the cell range string ".A2:B7" and converts the resulting string (eg "Sheet5.A2:B7") to an actual reference to a cell range that the outside VLOOKUP can use.
I have included the VLOOKUP functions' 4th parameter because I think you will only be interested in exact matches. Remove them if this is not the case.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
Todd Freyburger
Newbie
Joined: 17 Aug 2012
Posts: 4
|
| Posted: Fri Aug 17, 2012 7:20 pm Post subject: |
|
|
| I will have to try that later, thanks. |
|
| Back to top |
|
|
Todd Freyburger
Newbie
Joined: 17 Aug 2012
Posts: 4
|
| Posted: Fri Aug 17, 2012 10:07 pm Post subject: |
|
|
| The indirect command works, I eliminated the 0 because there are ranges involved in some spots and averages in others. |
|
| Back to top |
|
|
|
