Search results

Thank you so much Khalid NGO and NARAYANK991 - I did try the formula suggested by Khalid. It only replaced the first instance of the 5 digit "old text". Is there a way I can change the formula to look at the second instance, then the third, etc. That way I could apply it, then copy the...

I've attached a sample workbook... It seems like I've done some pretty complex stuff (complex for me) through the years, but sometimes I just get stumped. I've learned so much from Chandoo.org and all of you over the years. I'm sure I'm doing something wrong.

=SUBSTITUTE(O2,96360,3800009) -this is what I'm looking for except I'd like to pull the "old text" and the "new text" from a table? This substitute formula works but I don't want to have to enter the old and new values in the formula each time. :-( each cell string has several of the 5 digit...

I'm stuck. :-( any ideas anyone?

I noticed when I type in one of the actual values in my formula, for the 'old text', like 96360, the formula works. it goes to the vlookup table and returns the 'new text' 3800121. like this: =SUBSTITUTE(O2,96360,VLOOKUP('mainlist'!$A$2:$C$61, 'mainlist'!$A$1:$C$61,1,FALSE)). but there's so...

yes. darn. I tried converting my table data to text. some of my table values are numbers (i.e. 96360) and some are text (i.e. C7600). I assumed all of my original document values are text, because every cell has a combination of text and number. but some of the original values look like...

Hello, I’m trying to use the SUBSTITUTE formula to replace one value from a table with another value from that same table. I thought I could use a VLOOKUP table with the substitute formula. Here’s the formula I’m using. Column 1 on my mainlist table is the new text, column 3 on my mainlist...

Hello, I'm using one of Chandoo's great formulas, like this: {=SMALL(IF(IFERROR(SEARCH($G$2,$A$1:$A$20)>0,FALSE),ROW($A$1:$A$20)),ROW()-2)} to return the row number from an array. Rather than have the results in a vertical column, i'd like to drag a formula across a horizontal row. I was...

OMG... the &#60 is "<" (less than) So I used... =IF(COUNTIF(I:I,-I2)<COUNTIF($I$2:I1960,I2),"no match","") But... its marking the positive value with a "no match", but there is a negative match? The negative match isn't marked? it's odd...

I noticed I had a colon after the #60 so I changed it... =if(countif(i:I,-i2)&#60;countif($i$2:i2,i2),"no match","") I still get an error. And I don't know what the &#60 is for? -Thanks, Ron M.

I'm trying this... =if(countif(i:I,-i2)&#60:countif($i$2:i2,i2),"no match","") but it errors on the #60. What is that #60 for?

It looks like the issue i'm running into with this is that the cell I'm using as my source has more than one word in it. If cell c2 'only' has the single word "ring" these formulas work if the TABLE ARRAY 'only' has the single word 'ring'. But it c2 has 'ring a ding ding' then the formulas do...

Thank you SirJB7. I was not able to get that formual to work. With or without converting it to an array (ctrl+shift+enter). This formula returned NO for all lines. There are matches in both lists, it should find them. IFERROR(VLOOKUP(C2,'IV FLUID BAR CDM TABLE'!$E$2:$F$26,2,FALSE),"NO")...

Hello, I’ve got two sheets with columns of data. I need a formula to look at each word in each cell in column c on sheet one, and then search each word in each cell in column e on sheet two, and if it finds a matching word, then return the corresponding value from sheet two column f back to...

Hello, I’ve tried so many things I can think of but I just can’t get this one. I have a template with 4 column headers that I need to find their matching column headers on a second sheet, and then return the values in that column. For example, on sheet one, column B1 is ‘ITEM NUMBER’, so I...

Thanks NARA, I don't know how to upload a workbook :-( Thanks Faseeh, the price is not listed on the spreadsheet. One account number (column a) might be listed on 10 lines with the same or different item numbers (column b), the same or different dates of service (column c), and the...

This one is crazy. so If the value in column a, b, c, match then in column d, I want to find if there is a positive value with an offsetting negative value for those same a, b, c matches. a = account number b = item number c = date d = quantity (could be positive or negative. I need to...