Lookup Formula Help

[gronji2004]

Hello,

I am having an issue with a lookup formula returning the wrong value, please see below:

Basically in cell F43 (highlighted), I want to return the value of the cell in J:J that has a 1 next to it on the right hand side. Some of them work, some return the completely wrong value as has done above. What am I doing wrong?

As you can see below, the cell is returning 40, when it should be returning 30 (J27) as there is a "1" in the K27 next to it, and the formula is looking for value "1" to return J:J?

Im not a complete noob at excel, but im not super advanced either, any help would be greatly appreciated.

Thanks,
Alex

 

[FormR]

Hi, welcome to the board!

LOOKUP() requires the values in column K to be sorted in ascending order. You could try this instead:

=INDEX(J:J,MATCH(1,K:K,0))

 

[Scott T]

try

=INDEX(J:J,MATCH(1,K:K,0))

 

[gronji2004]

WOW! You guys are geniuses, thank you both for the speeedy reply

 

[gronji2004]

Hi Guys,

Having another issue with something similar. I've done the above using "=INDEX(J:J,MATCH(1,K:K,0))) and that returns the correct number. However with the formula that its set up, how do I get the cell to display 0 if that number is negative? I've tried using a if forumla after the above formula but get a circular reference warning.

Any ideas?

Cheers,
Alex

 

[gronji2004]

I've attached an image if thats any help:

 

[barry houdini]

Try using MAX, e.g.

=MAX(0,INDEX(J:J,MATCH(1,K:K,0)))

 

[Guru008]

=if (INDEX(J:J,MATCH(1,K:K,0))<0,0,INDEX(J:J,MATCH(1,K:K,0)))

 

[gronji2004]

Thanks for your help!

I'm still working on the same document. On another page, I've got a cell that is locked and displays something from another sheet depending on the contents of another cell. However the cell is locked like I need it to be.

Is there way of having a override button, EG if the contents of another cell has sometihng in it, it replaces it with that contents, if not it still keeps the original? See below:

AS you can see cell C7 contains data from another workbook, however I'm wanting if D7 contains sometihng, it will overtwrite C7. Sorry if i am not clear.

THanks

 

[gronji2004]

Sorry, hope this helps, the highligted cell is C7 which is locked. It returns a value from another workbook depending what is in C6. Im basically wanting to have it "overide" the contents of C7 a there is something in D7. If not I'd like it to work as normal returning the cell contents from the other workbook ....