complicated query match formula!!

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
336
Office Version
  1. 2021
Platform
  1. Windows
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try...

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

Adjust the range, A2:D100, accordingly.

Hope this helps!
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top