index and match Mid in a array

[nihad]

Hi everyone

is it possible to look for a value based on search value to be Mid and array to be Mid as well?

=INDEX(MID(AG6:AG160,6,6),MATCH(AF162,MID(AF6:AF160,6,6),0))

I have tried this but I get #N/A

My search value is column AF162 Mid 6,6 and my array table is AG6:AG160 but Mid 6,6, column index is AG

Many thanks

 

[Fluff]

Hi & welcome to MrExcel.
That is possible, can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[nihad]

here is an example

I have the debits on column AG, I want to create the creates based on search value on column AF "305-01"

THanks

1.4 Standard Fees Recharge Journal Feb 2021.xlsx
	AE	AF	AG	AH
5	Description	Account Code	Debit	Credit
116	Pension Admin	6935-305-01	50.25
117	Pension Admin	6935-305-04	-
118	Pension Admin	6935-305-02	12.08
271	Pension Admin	7935-305-01
272	Pension Admin	7935-305-04
273	Pension Admin	7935-305-02
315	Pension Admin	7935-362-01
Rchrg Feb21

Cell Formulas
Range	Formula
AG116:AG118	AG116	=ROUND(I116,2)
AF116:AF118	AF116	=6935&"-"&B116&"-0"&C116
AF271:AF273,AF315	AF271	=79&""&MID(AF116,3,9)

 

[Fluff]

Why are you trying to use the mid function of col AG?

 

[nihad]

I could use RIGHT function
to identify the search values my nominal accounts starts with 6935-xxx-xx therefore I want my search values based on xxx-xx
for example I have 305-01 , 305-02, 305-04 , I also have 330-01 etc

 

[Fluff]

That is column AF, I am asking why you are using MID on column AG?

 

[nihad]

Oh good point I don't actually need MID function on column AG

 

[Fluff]

Does it work if you remove it?

 

[nihad]

YAY it works now:

=INDEX($AG$6:$AG$160,MATCH(MID(AF273,6,6),(MID($AF$6:$AF$160,6,6))))

However if you look at AH273 it returns the value from AG116,
it should return the value from AG118

what wrong am I doing?

Thanks

1.4 Standard Fees Recharge Journal Feb 2021.xlsx
	AE	AF	AG	AH
5	Description	Account Code	Debit	Credit
116	Pension Admin	6935-305-01	50.25
117	Pension Admin	6935-305-04	-
118	Pension Admin	6935-305-02	12.08
271	Pension Admin	7935-305-01		50.25
272	Pension Admin	7935-305-04		0
273	Pension Admin	7935-305-02		50.25
315	Pension Admin	7935-362-01		7.69
Rchrg Feb21

Cell Formulas
Range	Formula
AG116:AG118	AG116	=ROUND(I116,2)
AF116:AF118	AF116	=6935&"-"&B116&"-0"&C116
AF271:AF273,AF315	AF271	=79&""&MID(AF116,3,9)
AH271:AH273,AH315	AH271	=INDEX($AG$6:$AG$160,MATCH(MID(AF271,6,6),(MID($AF$6:$AF$160,6,6))))

 

[nihad]

Ah just answered my question, I should have done exact match.

=INDEX($AG$6:$AG$160,MATCH(MID(AF271,6,6),(MID($AF$6:$AF$160,6,6)),0))