Multiple Data Search
Multiple Data Search
(OP)
I have 5 (Five) data inputs... one is variable and is dependant on the input value (so looking for a range) three are columns and one is a row... complicated I know.
I have looked at using Lookup, Index and Match.
Any suggestions or examples?
I have looked at using Lookup, Index and Match.
Any suggestions or examples?





RE: Multiple Data Search
=====================================
(2B)+(2B)' ?
RE: Multiple Data Search
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Multiple Data Search
A12 = Variable value
B12 = Input
C12 = Input
D12 = Input
E12 = Input
F12 = Output from table of values
RE: Multiple Data Search
If this is something you are going to use long-term, with additions and changes to the table of rates, then I'd suggest doing it in a database application.
If you want to do it in a spreadsheet anyway, let us know.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Multiple Data Search
RE: Multiple Data Search
RE: Multiple Data Search
In Column I have combined columns J,K and L:
=J3&K3&L3
Similarly in cell E13 I have combined the Courier, Service and Type entries:
=B13&C13&D13
So in F13 a Match function will return the right section of the table.
I have also added a horizontal match on the weight, and a separate match on the value, depending on the return from the main match function.
These are combined in an Index function to return the postage price.
It will take a fair bit of work to make it reliable and robust if you want to add new categories and services, etc though.
Also note you had a typo in postage in the main table, which was stopping Match functions from working.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/