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

Report •

#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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#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 = 1111111111Roger
MBQR!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


Report •

#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.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

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.

MIKE

http://www.skeptic.com/


Report •

#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.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#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?


Report •

#7
May 19, 2016 at 14:10:46
I think that #Value error is because its an array and you need to hit Cntrl+Shift+Enter and it works.

Report •

#8
May 20, 2016 at 07:16:36
A bit of clarification;

In your first post you state:

The value I want to return is in MBQR!R1:R3439

That is only One column.

Then you state:

I am looking to match ACES!I2 in one of two columns that have text in it within MBQR!G$1:T$3439.

Now it's Two columns.

And in all of your forumlas you use the Range MBQR!G$1:T$3439
which is 14 columns.

How many columns and rows are we dealing with
and which ones are they?

Searching some 48000 cells over 14 columns would
probably be best done with a Macro.

MIKE

http://www.skeptic.com/


Report •

Ask Question