Finding a value in a range of cells

[imike964]

Hi, I'm new to the forum but I'll try to be descriptive.

=IF(COUNTIF('Deal Calculations'!E4:E30,"Pandy Mandy"),'Deal Calculations'!B4,"£0")

Above is one of the formulaes that I am using and I tried editing, to no avail.

Anyway here it is, I need to search E4 - E30 for a particular name, lets say Pandy.
Now if pandy is found, then a cell on another tab has to equal a cell which is 2 cells on the left. from which the cell which contains the name is located.

here is a psuedo version of it...

Search E4-E30
Find a cell which contains 'Pandy'
If pandy is found, 2 cells to the left
then a cell on another tab is equal to that


Now I know that is confusing but I really need help and will try to clear this up if anyone asks any questions

[TexasKersh]

Hi imike

Can you upload a simple spreadsheet with an example? Doesn't matter if you have formulas in there, just comment as what you want the cells to do.

Should be simple enough to do, just need to see what and where. Obviously if you can redact any sensitive data or just create a simple example.

[haripopuri]

A sample workbook would be helpful.

[imike964]

Thanks for the replies, I have attached a sample worksheet which should help.

Thanks,

[TexasKersh]

So if I read this right:

The cell in the charles parker sheet would need to lookup all the cells in deal calculations and then populate Deal amount, and the clients name?

Couple of questions here as well. Would there ever be more than 5 deals to a name? (you have only allowed 5 rows in deductions)

[haripopuri]

Is this what you want...

[TexasKersh]

Sample Sheet.xls

Try this

[imike964]

Thanks for the fast response, Yeah @TexasKersh, that is want I had initially did in the beginning, but then I'm sure you noticed there are 27 columns. And There are a second set of cells which need to be checked as well, but once I know how to do the first one i can do the second.

Is there no way to do a check, and if it returns a result, place the cell which is located 2 cells left, into another cell..?

Sounds really confusing..

Thanks

[imike964]

Sorry I didn't see that question. There will be another section where all this information will reside.

Also there will be more than 5 deals from time to time.

The deductions section is for something else.