Searching worksheets

[sdyer03]

I'm working on a backoffice project for a marketing group I'm with. I've tried blending different formulas together and can't seem to find the correct mix for what I want.

I'd like to search a sheet and everytime a text value is found I'd like the formula to return a dollar value that is referenced a certain column:row from the found value.

In other words, if I search for "Mac" in my sheet and it shows in Cells A1, E1, and M:31, I'd like to sum the values that are in cells B6,F6,N36. I would then copy this formula to 5 other cells that look up different values based on different texts. The value figure location would change based on the text searched for.

See attached for a workbook that has the basic information of what I'm trying to get.

So on the sheet "Moving" You can see the colored cells. Those are drop down menus based on a table. When a rig is chosen it brings up a set of criteria. Once that data is entered I'd like to sum the "Paid" based on each rig and have that figure summed on the far right (W1:X6) which I can then take and do other stuff as well. But I want it to pull "Paid" based on the category. This sheet would be ongoing and so there isn't a specific order it would be done and I really don't want to have a different sheet for each rig.

LMK if its any dirtier than mud.

Stephen

[FDibbins]

It would help a lot if you actually added some data (especially value) to your file, so we can see what you are working with, and what you expect

[sdyer03]

See attached. Ignore the sum formulas, I was just lazy. What I want is to have the sums listed based on the truck selected and the dollar figure isn't in the same row for each truck.

[FDibbins]

If you can keep all the data on the same line, this will work...
=SUMIF($A$1:$U$1,X2,$B$6:$V$6)

What I did was, in your DD table, I made all the rows contain the same data

[sdyer03]

that's a lot more simple than what I was trying for. I could just put an empty cell at the top of the shorter categories and that would line them up. IF I didn't want to do that is there another way or would it just be too ugly to set up?

[FDibbins]

It would get a bit more complex, but probably do-able. I prefer to keep things simple, that way they are easier to adjust and troubleshoot.
Also, if you kept them all the same, you would only need 1 set of DD values

[sdyer03]

True. Thanks!

[FDibbins]

Happy to help