***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[earthworm]

I Tried this formula but it dosent work correctly

on the below mention data

E1 = 54
E2 = 45
E3 = 65

What i want it if the Range has E1 i want the result of E1 Which is 54 but its not working what could be the problem please advice

=LOOKUP(2^5,SEARCH($C$5:$C$7,$H$5:$H$7),$D$5:$D$7)

The Underline formula is important because i dont want to select single cell

 

[earthworm]

bumppp...

 

[barry houdini]

Typically you wouldn't have a range as the second argument of SEARCH in this set up (i.e. where you have H5:H7).

I don't understand your example in relation to that formula, you mention E1, E2 nad E3. Are they cell references, they don't appear in your formula?

Perhaps you can explain, with a small example, what you want to do. What data you have and in what cells and then what result you expect?

 

[earthworm]

Typically you wouldn't have a range as the second argument of SEARCH in this set up (i.e. where you have H5:H7).

I don't understand your example in relation to that formula, you mention E1, E2 nad E3. Are they cell references, they don't appear in your formula?

Perhaps you can explain, with a small example, what you want to do. What data you have and in what cells and then what result you expect?

This an example

E1 = 9:00
E2 = 10:00
E3 = 15:00

The Above is the grid based on this grid i have another colum where

random data is entered like

CELL A1 = M2
CELL A2 = E2
CELL A3 = E3
CELL A4 = E1

from this colum i want the formula to look the values from random above data , match the value from grid and enter the value in sheet2

Cell A1

I know i can also use vlookup but i want this formula to work.y is this showing same value check the attached sheet

 

[trankim]

THANK YOU THANK YOU THANK YOU for having this contest! I had a similiar work related problem that needed Barry Houdini's Formula!

Worked LIKE a CHARM!

 

[girishs]

Re: June/July 2008 Challenge of the Month

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

Hi Barry
Will u please explain what is the use of 2^15 in this formula.

 

[Oorang]

lol Spoken like someone who didn't bother to read all 28 pages of the thread (Can't image why not.) It's been explained... Ad nauseum.

 

[Ralph Gregory]

Re: June/July 2008 Challenge of the Month

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

Very Good But it needs to expand to take in the larger array and be able to cope with no colours found in the string
try
=IF(ISERROR((LOOKUP(2^15,SEARCH((OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$40),1)),Sheet1!A2),Sheet1!$E$2:$E$40))),"",(LOOKUP(2^15,SEARCH((OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$40),1)),Sheet1!A2),Sheet1!$E$2:$E$40)))
thats ugly so I split it up into 3 named formlas like this
1st Name "Item" =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$40),1)
2nd Name "DFind" =LOOKUP(2^15,SEARCH(Item),Sheet1!A2),Sheet1!$E$2:$E$40)
3rd Name "MyFind" =IF(ISERROR(DFind),"",DFind)
so my final Formula in the C Cells was
=MyFind


I have modded it to do that bu it looks way too long

 

[vassilg]

It's been almost 2 years with no new Challenge of the Month...

Any thought on that mrexcel?

Finding the most optimal solution is the biggest award!

 

[Ralph Gregory]

It's been almost 2 years with no new Challenge of the Month...

Any thought on that mrexcel?

Finding the most optimal solution is the biggest award!

yes I didnt notice the date of the challenge when I replied to the thread...bring us more challenges