# complicated query match formula!!

#### jpowell79

##### Active Member
Hi guys,

I have quite a complicated formula I need some help with...any help is greatly appreciated!

ok, where do I start!!

right, I have a worksheet called "Details" and in this worksheet I have a column (column H) which is full of data, starting from cell H5. Each cell in column H, each has a unique idenitfier, which is the last 8 digits of each cell.

These 8 digit numbers represent unique worksheets, which start with "Sheet" and then follow with the 8 digit number.

So for example,

Cell H5 may contain the following:

Monday 17th - Packard Bell: 21083209

Therefore there will be a worksheet called "Sheet21083209"

If you bear this in mind for a minute, I'll explain the other part.

Ok, now the cell to the right of H5 (i.e. I5) will contain a group of words, followed by a space then a colon ) and then a number.

For example, cell I5 might contain the following:

version 6.72 : 29023

Ok, what I need to do is find the word/s that come before the colon, in the correct worksheet adjacent to I5 (i.e H5) which will be somewhere in column A of the correct worksheet.

So for example, if cell I5 contained "version 6.72 : 29023", then somewhere in column A of worksheet "Sheet21083209" there will be "version 6.72"

ok, so once we establish exactly which cell that "version 6.72" is in in worksheet "Sheet21083209", I need the value found in the adjacent column D.

So if "version 6.72" was found in cell A21 in worksheet "Sheet21093209" then essentially, I want the value of cell D21

The final value would then be shown in cell A5 of the "Details" worksheet. Ideally this will be a formula I can copy all the way down column A. so A5 relates to H5/I5 and A6 relates to H6/I6 and A7 relates to H7/I7

hopefully that makes sense??

I'll do one more example.

Lets say cell H6 of the "Details" worksheet contains "Wednesday 29th - Sony: 21123339" and cell I6 contains "type 2.44 : 29023", then in worksheet "Sheet21123339", I want to look up "type 2.44" in column A, and find the value in the adjacent column D

I know, its a tough one!!!

I'd be forever grateful to the genius who can crack this!

thanks guys!!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Domenic

##### MrExcel MVP
Try...

=VLOOKUP(TRIM(LEFT(I5,FIND(":",I5)-1)),INDIRECT("'Sheet"&RIGHT(H5,8)&"'!A2:D100"),4,0)

Hope this helps!

#### jpowell79

##### Active Member
what can I say.... GENIUS!!!

thanks again

#### jpowell79

##### Active Member
This formula works very well, but the only thing is its very CPU intensive!

Am I right in thinking this is CPU intensive due to the INDIRECT and VLOOKUP elements of the formula??

Is there any way this formula could be re-written so that it's less intensive??

Thanks

anyone??

#### jpowell79

##### Active Member
I guess this one can't be improved then?

#### Domenic

##### MrExcel MVP
INDIRECT is a volatile function, which prolongs re-calculation times. How about changing the calculation mode to 'Manual'? Then calculate (F9 on Widows, COMMAND+= on Mac) as needed.

#### jpowell79

##### Active Member
Hi Domenic,

Unfortunately this isn't really an option for me (long story!) as this needs to work without any user intervention.

Could it be changed in any way to a non-volatile formula?

Replies
3
Views
479
Replies
7
Views
1K
Replies
9
Views
402
Replies
0
Views
673
Replies
0
Views
323

1,191,191
Messages
5,985,216
Members
439,948
Latest member
Xearo96

### 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.

### Which adblocker are you using?

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

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