VLOOKUP? INDEX? -- Excel Lookup Question

You can get the row number using this ARRAY formula

=MAX(($F$2:$F$8=1.5)*ROW($F$2:$F$8))

From this you can use the index formula to get your numbers.

What index formula??
Thanks.

I'm also getting a "7" as the returned value entering this formula on my example spreadsheet, which doesn't make sense as the 7th row start from F2 doesn't correspond to the last value 1.5 that I need to reference.

Is there any way you could write the formula on the example spreadsheet so it returns the 17.20 value I'm looking for and as I referenced on the spreadsheet?

Thanks, ssaqibh.

Copy-of-VLOOKUP.xlsx

Hello,

based on ssaqibh's formula you could build a named formula called "MaxRow" with a reference to a formula like this:

=MAX((Sheet1!$F$2:$F$10=Sheet1!$L1)*ROW(Sheet1!$F$2:$F$10))

In column L, starting from row 1, you can enter the desired value, for e.g. 1.50 in row 1, 1.25 in row 2, etc. The named formula will then refer to each of these values in column L individually.

Then in column M, starting in cell M1, you can use a formula like

=INDEX($C$1:$C$10,MaxRow)+(INDEX($D$1:$D$10,MaxRow)/60)

This way you don't have to use an array formula and the formula that calculates the end result does not have to do the Max/If calculation multiple times.

If you don't want to use the named formula approach, then the complete array formula would be:

=INDEX($C$1:$C$10,MAX(($F$2:$F$10=1.5)*ROW($F$2:$F$10)))+(INDEX($D$1:$D$10,MAX(($F$2:$F$10=1.5)*ROW($F$2:$F$10)))/60)

You decide what's easier to maintain and use.

cheers, teylyn
VLOOKUP.xlsx

This is more over my head than I thought it would be -- sorry. I would love to use a non-array, formula, but I can't get teylyn's to work in my example spreadsheet. The formula(s) keep returning a value of 8.3166667 instead of 17.2. Not sure what I'm doing wrong, as I'm entering it exactly as you proposed?

Did you check the sample file I posted? I put it up a few minutes after my post, so you may have missed it.

No I didn't ... just saw it now. Looking it over -- thanks.

The crucial bit is in defining the formula for the named formula.

=MAX((Sheet1!$F$2:$F$10=Sheet1!$L1)*ROW(Sheet1!$F$2:$F$10))

See that the reference to L1 is $L1. The $ sign is only before the L, not before the 1. That is important. This way, the formula will only look in column L, but will look in the current row.

When you create the named formula, make sure that the selected cell is in row 1. If the selected cell is in any other row, the results will not be correct.

This is called absolute and relative referencing. With the $ sign, the reference to the column L is made fixed, it will always refer to column L.

But the row number will depend on the currently selected cell and will be relative to the cell's position.

The actual spreadsheet I'm working with is much, much larger than the example one I included with my question. I'm looking up how to name ranges, and where to find the one you did for MaxRow, so I can transfer these formulas to my larger spreadsheet - I think that's where I'm creating problems for myself.

Is there any way to do this without having to create a named range MaxRow? I can't figure this out. If I replace the formula that is named MaxRow into the formula in cell M1, it returns an error. I don't know how to work with named ranges -- or more specifically, how to transfer them over to a larger spreadsheet. I had no idea it would be this complex ... I thought a simple VLOOKUP for the value 1.50 could return the Mn and Sc values I need.

Try this formula for the row number. No array entry, no named range

=LOOKUP(1100000,ROW(F2:F8)/(F2:F8=1.5))

>>Is there any way to do this without having to create a named range MaxRow?

Sure. I posted the formula without using a named range in my first reply. It includes the lookup using ssaqibh's original approach. It is an array formula and needs to be confirmed with CTRL-SHIFT-ENTER.

I see that ssaqibh and rorya have chimed in with alternatives, so I trust you will find something that suits your needs.

I know that named formulas can be daunting and it may take some time to get the concept. Don't worry. If you don't want to use them, that's fine. But then you may need the odd array formula to get the results you need.

If you could post a sample file that is more representative of your original workbook, it would not be any trouble to have a look and see what can be done.

cheers, teylyn

On teylyn's workbook enter this formula in M1 and copy it down.

=INDEX($C$1:$C$10,LOOKUP(1100000,ROW($F$2:$F$10)/($F$2:$F$10=L1)))+(INDEX($D$1:$D$10,LOOKUP(1100000,ROW($F$2:$F$10)/($F$2:$F$10=L1))))/60

I feel bad ... I've learned a lot in the last hour from ssaqibh and teylyn, and I really appreciate your help and patience ... but rorya's post just now did the trick, and is easy to work with.

Hopefully I explained my need as well as I could have, and didn't waste anyone's time. Like I said, I learned a lot in the process nevertheless, I appreciate all your help, and thank you rorya for jumping in with your post that really does exactly what I need.

Talk to you all soon, and thanks again.

No worries. Glad you learned something. And we all know rorya is one of the best.

I second teylyn