VLookup w/o column index no.?

[jenl387]

Is it possible to create a look up table when there is not a definite column index? This is for a tax table. Example:

Col A Col B Col C Col D Col E
(at least) (but less than) (subtract) (multiply result by) (add to result)
4500 5500 50 .20 10

I am not used to these types of tables, any help would be appreciated.
Thank you!

[tlafferty]

Hi jenl387, and welcome to the forum! I'm pretty sure I can accomplish what you need but would appreciate a sample workbook to test. Here's how to attach one:
Click the Go Advanced button, then click the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Thanks!
Tom

[jenl387]

Hi jenl387, and welcome to the forum! I'm pretty sure I can accomplish what you need but would appreciate a sample workbook to test. Here's how to attach one:
Click the Go Advanced button, then click the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Thanks!
Tom

Thank you Tom. Here goes.

[scottylad2]

THink this does what you need =SUM(INDEX($L$6:$L$26,MATCH(F9,J6:J26,1)+1)*0.08,INDEX($P$6:$P$26,MATCH(F9,J6:J26,1)+1))

which yeilds the result 270,000

[jenl387]

THink this does what you need =SUM(INDEX($L$6:$L$26,MATCH(F9,J6:J26,1)+1)*0.08,INDEX($P$6:$P$26,MATCH(F9,J6:J26,1)+1))

which yeilds the result 270,000

Thank you Scott! However, when I do the calculation maunally the number should come out to 133,568. I plugged in a formula (f12) to get that result. What I'd like to do is be able to use any number in f9, have it refer to the table and bring back the correct result.

I don't know if it's possible, someone in my office thinks it is and it's now my job to find out.

Thanks for all your help!

[NBVC]

How about?

=(F9-LOOKUP(F9,H6:H26,L6:L26))*LOOKUP(F9,H6:H26,N6:N26)+LOOKUP(F9,H6:H26,P6:P26)

[jenl387]

Perfect! Thank you!

[NBVC]

If you are satisfied with the solution provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save