Lookup / Find / Search values and return same

[Pereg]

Hi,



Is there a way to search for an amount/text in multiple different rows and columns when specific conditions are met, for instance first find A (constant) ,then B (constant), then C (variable) and return C (variable) ; then loop and find D (constant), then E (constant), then F (variable) and return F (variable). B and C are always in the same row as well as E and F.

The attached example will give you a better idea.:beerchug:

[Ismael]

Hi Pereg,

Can you be more specific in your example.....
Because I don't full understand what you want.
In fact is possible to make a formula that based on same criteria's can return a value, but you will have to clarify your doubt.

Ismael

[Pereg]

Thanks for responding to my post.
Essentially what i'd like is to look up a value in the first column of a table array and return a value in different row from another column in the table array. I have modified the sheet to give you a better understanding; the end result are in the 'Result' sheet.

[Pereg]

Hi

There is a method of the returning the 'amount' values in relation to the 'account' values using SUMPRODUCT function.

The only hiccup with this function is the irrelevant text between the relevant values.

Attached is an example; my original worksheet is huge that's the reason why i prefer not to copy and paste.:bug:

[Ismael]

Hi Pereg,

it seems that I find a possible solution for you, I just insert 1 column in your sheet "rawdata" in order to associate the total whit the number that you want to lookup. The only thing that you have to be careful is that you always have to sort your table in crescent order.

See the example and try it whit your original worksheet and then let me now if exist any problem.

Regards,

Ismael

[Pereg]

Hi Ismael,

Wow that's a wicked formulae, simplicity to the tee but effective to the bone.

You know what, the only glitch I can think of is the fact that the accounts on my Original RawData sheet are not sorted in ascending or descending order and i can't think of a way to sort by account number that won't effect the integrity of the data.

Thanks,

[Ismael]

Hi Pereg,

I think I just solve the sort problem.

See the attach file and tell me if it's ok now.

Regards,

Ismael