MS Excel 2016 Replace function help

woodsonh09

New Member
Joined
Apr 22, 2016
Messages
1
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

Active Member
Joined
Apr 20, 2016
Messages
291
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

Active Member
Joined
Jan 9, 2014
Messages
473
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)
 
Last edited:

Forum statistics

Threads
1,082,641
Messages
5,366,717
Members
400,915
Latest member
NFlem

Some videos you may like

This Week's Hot Topics

Top