# Excel Formula needs to add Index to return cell information

May 19, 2016 at 07:06:01
Specs: Windows 7
 Hello, Currently I am working with two tabs of data that do not have the same unique identifiers at the same time. I am using a series of functions to search and match a number in a text string then it returns the row location of the match. Here is the current formula. I am thinking that if I can get this far then I can add Index some how to return the cell value I need. =IF(COUNTIF(MBQR!G\$1:T\$3439,"*"&ACES!I2&"*")>=ROWS(1:1),SMALL(IF(ISNUMBER(SEARCH(ACES!I2,MBQR!G\$1:T\$3439)),ROW(MBQR!G\$1:T\$3439),""),ROWS(1:1)),"")The value I want to return is in MBQR!R1:R3439. Is this possible?

See More: Excel Formula needs to add Index to return cell information

#1
May 19, 2016 at 07:49:30
 Before I can offer any suggestions, I need an explanation of this portion of your formula:COUNTIF(MBQR!G\$1:T\$3439,"*"&ACES!I2&"*")Specifically, I don't understand the construct of the criteria argument for the COUNTIF."*"&ACES!I2&"*"What are you concatenating asterisks fore and aft of the range reference? That is causing a #REF error.

#2
May 19, 2016 at 07:54:40
 It works when I use it as I am searching in text strings for a number match. In the search parameters I am looking to match ACES!I2 in one of two columns that have text in it within MBQR!G\$1:T\$3439. Example ACES!I2 = 1111111111 MBQR!G8 = 1111111111RogerMBQR!T8 = Hi my name is 1111111111 That formula tells me there is a match in row 8 and returns 8 with that asterisk setup. message edited by phatguy85

#3
May 19, 2016 at 08:15:36
 OK, my fault. I created the ACES sheet after I pasted the formula into a cell and the formula did not recognize the new sheet name, resulting in an #REF error.Now that that has been taken care of the next issue appears to be with this section of the formula:SEARCH(ACES!I2,MBQR!G\$1:T\$3439)The within_text needs to be a text string, not a range. The argument could be a reference to a single cell that contains text, but unless I'm mistaken, it can't be a reference to a range, e.g. MBQR!G\$1:T\$3439,The Formula Evaluate throws up a #VALUE error when it tries to evaluate the within text argument when it contains a range, but works fine with just a single cell reference.(Please keep in mind that we can't see your workbook from where we're sitting, so we're flying kind of blind here, just trying to set up a workbook to test your formula.)

Related Solutions

#4
May 19, 2016 at 11:07:24
 What is it your searching for?Is it simply a list of numbers or a list of words?Why do you need the Row number of the match, is it for further processing?What exactly are you doing?There may be an easier way.MIKEhttp://www.skeptic.com/

#5
May 19, 2016 at 13:46:07
 Instead of SEARCH() try using MATCH() & ADDRESS() something like:=ADDRESS(MATCH("*"&ACES!I2&"*",MBQR!G\$1:T\$3439,0),1)This is untested. Not sure the multi column range is MATCH() will work.MIKEmessage edited by mmcconaghy

#6
May 19, 2016 at 14:06:29
 =IF(COUNTIF(MBQR!G\$1:T\$3439,"*"&ACES!I2&"*")>=ROWS(1:1),INDEX(MBQR!\$R\$1:\$R\$3439,SMALL(IF(ISNUMBER(SEARCH(ACES!I2,MBQR!G\$1:T\$3439)),ROW(MBQR!G\$1:T\$3439),""),ROWS(1:1))),"")this seems to work with Cntrl+Shift+Enter for the array but it doesn't account for duplicates. If ACES!I2 shows up more than once on the ACES tab it only returns the first hit twice on the MBQR Tab. Goal is to return a cell content by searching for ACES!I2 in the MBQR range which can be a text string as mentioned above. I don't need the row number of the match it was just part of the process to get to using INDEX. All other functions I tried would not work. SO with that said any way to use the above mentioned to not duplicate?