VLOOKUP down -> up

Hi,

I want to search for the 1st number 1 imediate above the number 4 in the same column. (please see the attached file)
I've tried VLOOKUP but it only functions UP-DOWN and not DOWN-UP....

Can anyone help me?

Good day,

Instead of using the Vlookup, use a combination of INDEX & MATCH functions to obtain the result. Please refer attached.



Mark the thread as SOLVED in case your problem is resolved.
Press the star icon if this post has been helpful.

Hi,

I think i didn't explain myself correctly...what i want is the position of the number 1, or its address...
So i want to search for the 1st number "1" imediate above the number 4.

Thanks

You should provide an example that better shows what your data may look like - the sample you provided would just need a MATCH() performed on the range of numbers.

If there a no equals, this one wil do.

If there are no equals, this one will do.

Array confirmed with Shift Ctrl Enter

=MATCH(2,IF($C$7:$C$11=1,1,IF($C$7:$C$11=4,4)))

Cutter, those formula both appear to take the location of the last cell with a value of 1 as long as at least one cell = 4, not the last cell with a value of 1 above the last cell with a value of 4 (see posts #1 and #3)

Best effort from me so far is

="C"&MAX(IF(C3:INDEX($C$3:$C$19,MAX(IF($C$3:$C$19=4,ROW($C$3:$C$19)-2)))=1,ROW(C3:INDEX($C$3:$C$19,MAX(IF($C$3:$C$19=4,ROW($C$3:$C$19)-2))))))

Array confirmed.

Originally Posted by Cutter

Try this array formula (confirmed with Ctrl+Shift+Enter)

=IF(COUNTIF(C3:C19,4),"C"&MAX(IF(C3:C19=1,ROW(C3:C19))),"")

or

=IF(ISNUMBER(MATCH(4,C3:C19,0)),"C"&MAX(IF(C3:C19=1,ROW(C3:C19))),"")

Dear Cutter,

Apparently it works i just don't understand why the if statement, becaus the following works:

="C"&MAX(IF(C3:C19=1,ROW(C3:C19)))

I'm going to see now jason.b75 last reply....

Thanks in advance

Originally Posted by jason.b75

Cutter, those formula both appear to take the location of the last cell with a value of 1 as long as at least one cell = 4, not the last cell with a value of 1 above the last cell with a value of 4 (see posts #1 and #3)

Best effort from me so far is

="C"&MAX(IF(C3:INDEX($C$3:$C$19,MAX(IF($C$3:$C$19=4,ROW($C$3:$C$19)-2)))=1,ROW(C3:INDEX($C$3:$C$19,MAX(IF($C$3:$C$19=4,ROW($C$3:$C$19)-2))))))

Array confirmed.

Dear jason.b75,

I've tried it several times and it appears to work fine, but for me is very very complex...i analised avery step but i couldn't understand the logic behing this...
OK, if you think you can explain quickly what is the logic i'll appretiate it, but if not i am pleased already with the solution you gave!

Thanks very much!

I found an error using it: whe you cut/paste the matrix with the values in another place, it returns an error #REF.....
Can you try this please??

Thanks!

Originally Posted by jason.b75

It's much the same as

="C"&MAX(IF(C3:C19=1,ROW(C3:C19)))

But C3:C19 is replaced with C3:INDEX($C$3:$C$19,MAX(IF($C$3:$C$19=4,ROW($C$3:$C$19)-2)))

which resizes the range to "C3:last cell with 4" so that any cell with a value of 1 after the last cell with 4 are excluded. The MAX(IF()) part returns the last row with value of 4, the -2 is a correction factor as the data starts in row 3 of the sheet.

edit:

The error is probably coming from pasting to different rows, linking the correction factor to the top row of data should fix it.

="C"&MAX(IF(C3:INDEX($C$3:$C$19,MAX(IF($C$3:$C$19=4,ROW($C$3:$C$19)-ROW($C$3)+1)))=1,ROW(C3:INDEX($C$3:$C$19,MAX(IF($C$3:$C$19=4,ROW($C$3:$C$19)-ROW($C$3)+1))))))

Dear jason.b75,

I'm very very pleased with your answer and your explanation.
It works just fine!!!

THANK YOU VERY VERY MUCH FOR YOUR ATTENTION!!!