Mutliple criteria lookup to return a value

Hi,

Im trying to use a formula to generate a value based on the results of three cells.

The idea is that you select a company name from Col A, a resource type from Col B (i.e labourer etc..), and a time from Col C (Day/Night etc..) this will then generate a rates based on the combined results from Col A, B & C.

The source data is on the second worksheet and is organised as a list with column headers. I would normally use a VLOOKUP but it is not fleixlbe enough to account for three criteria.


Col A Col B Col C Col D
Company Name Resource Type Day / Night / Weekend Shift FORMULA GENERATES RATE BASED ON COL A, B & C CRITERIA

Any help much appreciated

Thanks


Jim

I would recommend posting a sample worksheet, as the best method to tackle this issue would require visualizing the data.

Without having seen the data, would it be possible to use 3 vlookups in a sort of mathematical calculation? For example, each company has a rate, each resource type has a rate and day/night each have a rate. Then you could possibly use Vlookup(Company)*Vlookup(Labor Type)*Vlookup(Day/Night), or something along those lines?

Hi,

Thanks for that, I dont have a formula sample as Im unsure which way to approach it. Col A, B & C are all text data any based on these selections I am looking to return a relevant rate in Col D, i.e Pick the Agency (Col A), pick the resource type (Col B), pick the time (Col C) based on these three criteria a lookup finds the correct hourly rate from the table of data and shows it in Col D.

Hope this makes sense

Thanks jake.masters I will attempt to post a sample worksheet shortly

I think this should work ! attempt to upload No 1 !

Multiple Criteria Lookup.xlsx

Hi Fotis,

Partly yes.... the Appoved Electrician is £18ph but not from Bourne as in in the sample - Bourne do not supply Approved Electricians I would need the formula to return an error or N/A to show the resource is not provided by that agency, if that makes sense....

dwint tanks very much works a treat !

Much appreciated thanks to all who replied

@ JIMH1980

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Hi dwint,

I may have celebrated a bit early on Tuesday ! The sample sheet you sent through works great of the first sample but once I change the agency and role and move the flormula down the sheet the results or not correct.

Would you bo so kind as to take a look and let me know if it's me being stupid ? Let me know if you would like me to upload the sheet again

Thanks

Jim