Struggling Again With A VLOOKUP

[JohnK]

Hi

I'm having a little trouble with a VLOOKUP function where I have a
range of values spread over 9 columns with the lookup value appearing
somewhere in the first column. What I need to do is have the function
select the appropriate column based on yet another criteria. So, say I
have a value that gets looked up in the first column as $500, and in
one of the other columns (Column C, say) in the range corresponding to
the looked up value is $350 (which is the $500 less tax say), but each
of the columns represents a different tax rate depending on this other
criteria.

Am I able to use VLOOKUP where Value A ($500) gets looked up in Column
A, but where Value B determines which column to go to across from the
looked up value?

I have a feeling I'm barking up the wrong function here, but I hope
this makes sense. I would be grateful for any suggestions.

JohnK

 

[Aladin Akyurek]

You didn't say how column B specifies the return range for lookup. If a
3 in column B means the 3rd column from the lookup range (column A) on then:

=VLOOKUP(LookupValue,A:J,VLOOKUP(LookupValue,A:B,2,0),0)

 

[Arvi Laanemets]

Hi

=VLOOKUP(LookupValue,LookupRange,NumericExpression,0)

LookupRange must be a continuous range, and must include the column with
lookup values as leftmost, and must include all tax rate columns.
NumericExpression must return an integer, representing the number of column
with wanted tax rate in LookupRange (the leftmost column - the one
containing lookup values - is 1st one).

P.e. with value to look up in A2, LookupRange in Sheet2!$B$2:$K$1000, and
the wanted tax group (integer values between 1 and 9) stored in C2:
=VLOOKUP(A2,LookupRange in Sheet2!$B$2:$K$1000,C2+1,0)

(3rd parameter is calculated as C2+1, because tax rates are in columns 2 to
10 of LookupRange)

 

[Guest]

Assuming column B contains an integer between 1 and 9 (3 for Column C, 4 for
Column D etc) then:

=VLOOKUP(A2,Sheet2!$A$2:$I$20,VLOOKUP(A2,Sheet2!$A$2:$B$20,2,0),0)

Hope this helps
Rowan