MS Excel 2016 Replace function help

[woodsonh09]

Hello everyone,

I am working on an assignment and for the life of me cannot figure this out. Below is my spreadsheet:
Imgur: The most awesome images on the Internet

I am trying to replace the B column that is 1-64 with their respective Division names in Column H. I have my formula posted in the photo but for some reason it just wont work.

Please help me!

Thanks Everyone, Happy Friday

 

[XLearner]

Hi,

You need to use an extra column (B3:B54 contain values so you can't use any formula on top of that w/o creating a circular ref. This is called out by the blue bubble in B3 and at the bottom left of your spreadsheet) and REPLACE is not right function for what you want to achieve.

In F3 enter :

=INDEX($H$3:$I$67, MATCH(B3,$I$3:$I$67,0), 1) and hit [Enter]

Copy down the formula until row 67

Once done you can select F3:F67, Copy, Paste Special the Values in B3 and finaly delete the content of F3:F67

Regards
XLearner

 

[ghrain22]

That's not how the replace formula works.
The replace formula is for replacing text in a cell with alternative text.

My understanding is that your trying to do a lookup type formula based on the BVSN number. In which case adding another column to "table 1" would be helpful.

If Table 2 had columns H and I reversed, then this could be easily accomplished with a vlookup. However in the current layout you would have to do a index match.

Assuming we inserted a column between DVSN and Startdate in Table 1, the formula would look something like this:
=index($H$3:$H$67,match($B1,$I$3:$I$67,0)

If you switch columns H and I then you can do a vlookup which is a bit simpler and would look like this:
=vlookup(B1,$H$3:$I$67,2,False)

 

[0101]

Hello,

Click Me!! to know how the REPLACE Function works in Excel.

Cheers