very quick one

[jpowell79]

Hi Guys,

I'm trying to grab the cell of an adjacent cell, based on the value in cell b42

cell b42 is an 8 digit cell, eg 24330182

Now column H between cell H5 and H32, contains data, with the final 8 digits in each cell being an 8 digit number,

e.g. cell h23= wednesday 24.06.06 aa 24330182

Now what I want to do is match the cell b42 to the correct cell in column h, and then once I know the correct cell, grab the value in adjacent column A

For example,

cell b42 = 32118273

Cell H11 = friday 19.93.05 cc 32118273

Therefore grab the value of A11


I was trying to use this formula, but it doesn't work

=INDEX(A5:A32, MATCH(B42, RIGHT(H5:H32, 8)))

any ideas what I'm doing wrong??

 

[phxsportz]

If H11 is a HIT (so to speak) use the OFFSET function to grab A11

ie =OFFSET(H11,0,-7)

 

[jpowell79]

Hi phx,

But it's not always H11.....it might be H18, H6, H2, etc....

It always depends on the value of cell b42, which is always changing

 

[phxsportz]

What's the criteria for the change ?

 

[pgc01]

Hi jpowell79

Your formula is almost perfect. You need the 3rd parameter of MATCH, I've just tried it and it worked.

=INDEX(A5:A32, MATCH(B42, RIGHT(H5:H32, 8),0))

2 things you may be doing wrong

- The result of RIGHT is text. Is B42 a text value? Try =ISTEXT(B42). If it's not text, format the cell as text and then enter the number.

- This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER. Are you doing it?

Anyway, as I said it worked for me.

Hope this helps
PGC

 

[barry houdini]

Try

=INDEX(A5:A32,MATCH("*"&B42,H5:H32,0))

 

[jpowell79]

Hi Barry,

that works great..... just out of curiousity, is there any way to show a 0 (instead of #N/A) if b42 DOESN'T match any of the values in column H??

something like

=IF(ISERR(INDEX(A5:A32,MATCH("*"&B42,H5:H32,0)), 0, INDEX(A5:A32,MATCH("*"&B42,H5:H32,0)))

Although the formula I just put doesn't work lol!

 

[jpowell79]

not to worry,

I'm using:

=IF(b42=0, 0, (INDEX(A5:A32,MATCH("*"&B42,H5:H32,0)))

which seems to work ok

thanks barry

 

[barry houdini]

Sorry, it was past my bedtime

Your second formula should work fine if the only circumstances in which there won't be a match is when B42 is equal to zero. If there could be other numbers in B42 where there is no match then you have to use something similar to your original attempt.

ISERR doesn't work here because the only error it doesn't check for is #N/A. You need the opposite, ISNA, so try

=IF(ISNA(MATCH("*"&B42,H5:H32,0)),0,INDEX(A5:A32,MATCH("*"&B42,H5:H32,0)))