Multiple Data Search

[Flatmate]

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?

 

[electricpete]

please explain a little more what you've got and what you're trying to do.

=====================================
(2B)+(2B)' ?

 

[IDS]

A spreadsheet showing what you have got so far would be helpful.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Flatmate]

please find attached the spreadsheet...

A12 = Variable value

B12 = Input
C12 = Input
D12 = Input
E12 = Input

F12 = Output from table of values

 

[IDS]

Unless I'm missing an easy way (which is possible), I don't think that's going to be easy in a spreadsheet, but it should be straightforward in a database.

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/

 

[Flatmate]

if it is possible, I'd like to see if it can be done using a spreadsheet.

 

[zelgar]

Based upon your current table, you don't have all of the data for the choices (e.g., you currently don't have any results if its myHermes & Courier_Collection). Additionally, there's a column of data (Col M) in your spreadsheet that isn't factored in your data from Col A - E. Is this related to insurance of the item/ it's costs (e.g., for MyHermes, Pacel_Drop_Off, Parcel in col M you have values from 0 to 250.1)

 

[IDS]

See the attached file for some ideas:

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/