Hi Dick,

Thanks a lot for your prompt and great reply.

Finally I manage to solve the scenario 1 and it is due to the value in
the range C2:F2 mismatch with the variable in J14, ie the text string
too long.

Thanks again for your kind offer of your example workbook

Regards
Lenard



"Dick Kusleika" wrote in message ...
Lenard

Sure

=VLOOKUP(
lookup_value = J13 = taxable income,
lookup_range = A3:F102 = whole table,
return_column = MATCH(
match_value = J14 = filing status,
match_range = C2:F2 = range showing filing status,
estimate = FALSE = get an exact match)
+2)

In C2:F2, you should have a range of filing statuses. Mine look like this

Single MFJ HOH MFS

The MATCH function will return a number based on where J14 is in that list.
If J14 = Single, then the MATCH function will return 1 because Single is the
first entry in the match_range. Since the lookup_table is A:F, I add two
the MATCH function to sync it with the whole table. For instance, if J13 =
MFJ, the MATCH function will return 2 (the second item in the list) but that
will mean that I want the 4th column of the table, so I add 2.

If you're getting N/A, it almost certainly is that the value in J14 does not
match any values in C2:F2.

If you like, I can send you my example workbook.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


"Lenard" wrote in message
...


Hi Dick,

Thanks for your suggestion and it works fine for the above scenarios
except Scenario 1.

It seems that the formula will completely ignore the changes made in one
variable (cell J14), particularly the Filing Status is changed to "
Single " with any Taxable Income, say 51,155 or 50,125, the result shown
will still be "#N/A"

I'd be much appreciate if you could briefly explain the formula,
particularly with " +2 " towards the end of vlookup formula.


Regards
Lenard



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!