I'm trying to have create a function that will scan done of a range of cells, and if there's a match to a certain string or one of a number of strings,
then it will put in the next column over a specific string.
My gut tells me IF INDEX and MATCH will do the trick together.
Is my reasoning wrong?
No, it isn't, however, to confirm we'll need to see your workbook.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The Excel file's rather large, so here's a screen clip of the portion that should help.
I just want to use the function to find a word that's in Col B and if it finds it,
inserts a different word or two in Col C.
I will use the function multiple times, changing the word it's looking for in Col B and what word to add in Col C each time.
T?hanks!
So you have an incomplete list in column C and you need to fill in the blanks?
Mid Sussex in column B always = West Sussex in column C?
To do it without a lookup table, try this method
** Note that this will generate a circular reference error if there are consecutive blanks in column C with no correct match immediately above the first blank **
On the assumption of no blank cells in column B, data starting in row 2.
Select B2, Press Shift Ctrl Down, Shift Right, Tab, Shift Right. If done correctly, this will select all of the cells in column C , blank and non blank, where column B is completed.
Press Ctrl g, click Special, Blanks, OK.
Copy this fomula and paste it into the formula bar, then press Ctrl Enter, not just Enter!
=IF(INDIRECT("R[-1]C[-1]",0)=INDIRECT("RC[-1]",0),INDIRECT("R[-1]C",0),IF(INDIRECT("R[1]C[-1]",0)=INDIRECT("RC[-1]",0),INDIRECT("R[1]C",0),""))
Select C2, Press Shift Ctrl Down, Ctrl c, Ctrl Alt v, v, Enter to covert the formulas to text.
Correct, Column C is a incomplete list, several values in Column B will have the same value in Column C.
There's about 48 different possibilities that will go into Column C based on the value in Column B; each value for Column C based on about 10 or so values in Column B.
The Excel doc is about 400,000+ rows, so I'm trying to be sure before I put in the formula.
I sort of figured I was going to do it manually 48 times or use a lookup table.
I am trying to understand your formula as you have it there, but my gut tells me it's not going to work for my situation using that process, but I'm still going over it and trying to see.
I may end up tweaking the formula some more.
if xlsx is too large to post use xlsb format (up to 9.77 MB) and try again
Last edited by sandy666; 07-08-2018 at 03:38 PM.
Why not copy 100 or so rows to a new file for testing?
The method that I suggested will apply the formula to the blank rows.
For example, C10 is empty, If B9 and B10 are the same then the formula in C10 will copy whatever is in C9
If B9 and B10 are not the same then the formula will look at B10 and B11, if they are the same then it will copy whatever is in C11 to C10.
The formula will work with consecutive empty cells when a match is found in the row above, but not when it has to look at the row below.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks