Index / Match problem

[JO505]

The other day I saw a formula from "ChemistB", and I am having a problem with it that I cant seem to figure out.

In the example attached, everything works fine for the first 2 selections (numbers 100 & 101 from the dropdown box) I cant get values to return for selections above 101. I don't know if it is something in the formula or the structure of the sheets or something else I don't see.

Thanks in advance.

Jim O


Thread I got the formulas from.

[FDibbins]

Somehow you got the formula a bit messed up...

=IFERROR(INDEX('LOG 1'!N$2:N$1000,MATCH(ROW($E1),'LOG 1'!$T$2:$T$1000,0)),"")

[Lemice]

Hello,

This is your formula
=IFERROR(INDEX('LOG 1'!N$2:N$1000,MATCH(ROW(E3),'LOG 1'!$T$2:$T$1000,0)),"")

And this is how it should have been
=IFERROR(INDEX('LOG 1'!N$2:N$1000,MATCH(ROW(E1),'LOG 1'!$T$2:$T$1000,0)),"")

I have fixed all of your formula on the attached file.

Also, sorry about the format, I was dragging the formula ...

[JO505]

FDibbins,

Thank you very much. That works much better.


Obviously I don't understand the formula as well as I thought. Would you explain what ROW($E1) does? I thought it was referencing the number I wanted to search for like a cell reference "$cell number". I don't quite understand how the reference is being made.

Jim O

[FDibbins]

Thats exactly what it's doing, you are correct. We use ROW(E1)...could be any column...to increase a count in a formula as it gets copied down. ROW(E1) = 1, ROW(E2) = 2 etc. BUT by referencing row(E3) right from the start, instead of E1, it is returning a 3 instead of a 1.

Actually because of that, even when it was returning values, it was missing the 1st 2 matches every time - you only picked up the error when you asked for something that only has 1 entry on sheet1.

Hope that helped?

[popipipo]

ROW(E3)-2 returns the value of 1
or put just the formula ROW(E1) in row 3

[JO505]

Thanks to both of you for your response.

I'll try to remember that, but as "they" say when you get older memory is the second thing to go..... I forget the first!

Jim O

[FDibbins]

Im not sure that "they" say that or not, I cant remember any more