very quick one

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
331
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 :(

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

any ideas what I'm doing wrong??
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
If H11 is a HIT (so to speak) use the OFFSET function to grab A11

ie =OFFSET(H11,0,-7)
 

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
331
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi jpowell79

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

Code:
=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
 

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
331

ADVERTISEMENT

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

Code:
=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! :biggrin:
 

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
331
not to worry,

I'm using:

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

which seems to work ok :)

thanks barry
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)))
 

Forum statistics

Threads
1,141,717
Messages
5,708,066
Members
421,543
Latest member
SGM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top