What would be a perfect formula to get Display of Merged Cell Reference ONLY of Value in One sheet of Particular Col which is matched in another

RapchikM

Board Regular
Joined
Oct 5, 2020
Messages
97
Office Version
  1. 2021
Platform
  1. Windows
Hello

What would be a perfect formula to get Display of Merged Cell Reference ONLY of Value in One sheet of Particular Col which is matched in another sheet with addtional text or rather the Matched or Looked up value is contained with other text ?

I tried seprataing but the structure got completey haywired

Any chances with your utmost help and Mastered knowledge will I be able to derive using a perfect Formula in Sheet4

Am sorry i forgot to use XL2BB and Please Excuse me

Sheet 1 Structure

Col D

Row 5 494667020303913
Row 6 693667020303913
and So On........
Blank rows are also there
Row 31 58633702030913
Row 32 23020766372913


Sheet 2 Structure

Col D Merged with Col E with additional text Int:XXX and Tax:XXX

Row 15 494667020303913 Int:837.00 and TAX:83.00
Row 16 693667020303913 Int:837.00 and TAX:83.00
So on.......
Row 48 58633702030913 Int:3350.00 and TAX:335.00
Row 49 23020766372913 Int:3350.00 and TAX:335.00


Sheet 3 Structure

Col D Merged with Col E with additional text Int:XXX and Tax:XXX

Row 15 58633702030913 Int:3350.00 and TAX:335.00
Row 16 23020766372913 Int:3350.00 and TAX:335.00
So on.......
Row 25 494667020303913 Int:837.00 and TAX:83.00
Row 26 693667020303913 Int:837.00 and TAX:83.00

Sheet 4 Result Deisred
Col A Col B Col C Col D
Row 4 Sr.No In Sheet 2 Cell Ref In Sheet 3 Cell Ref
Row 5 1 494667020303913 D15 D25
Row 6 2 693667020303913 D16 D26
Blank Rows Copied too as per Sheet1
Row 31 3 58633702030913 D48 D15
Row 32 4 23020766372913 D49 D16
So on

RapchikM
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sheet 4 Result
Sr. NoIn Sheet 2 Cell RefIn sheet 3 Cell Ref
1​
494667020303913D15D25
2​
693667020303913D16D26
3​
58633702030913D48D15
4​
23020766372913D49D16

While Posting #1 i made error of Rows and Serial No. Kindly excuse

RapchikM
 
Upvote 0
Hello Guys
After Much exploration i tried the following method

=CELL("address",INDEX(Sheet2!$D$15:$D$174,MATCH($B2&"*",Sheet2!$D$15:$D$174,0)))

The Result that i get is
'[Trial.xlsx]Sheet2'!$D$15 in Cell C2 instead of D15 only
How to Remove the File Name, Sheet 2, and $ representation


RapchikM

 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,601
Members
452,658
Latest member
GStorm

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