Number v TEXT confusion

[Carty]

Hello

I have a formula that looks at the contents of cell G4 anduses the data to reference a specified column in another workbook:
=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH($G$4,'[workbook]worksheet'!$O$1:$O$65536,))
This works fine as long as the data in G4 is a number. Ifthe cell contains a string (e.g. AB 12345CDE) the result is #N/A.

I can isolate the number by using MID:

=MID($G$4,4,5) which returns 12345 but if I substitute the MID term in the MATCHterm:

=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH(MID($G$4,4,5),'[workbook]worksheet'!$O$1:$O$65536,))I get the same #N/A error.
Have I unwitting created a text term that cannot be used tosearch for a number or is the methodology wrong?
Thank you

Paul

 

[Special-K99]

When substituting a string add zero, to turn it into a number, ie

=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH(MID($G$4,4,5)+0,'[workbook]worksheet'!$O$1:$O$65536,))

or this should cater for either text or number in G4

=INDEX('[workbook]worksheet'!$H$1:$H$65536,MATCH(MID(IF(ISTEXT($G$4),MID($G$4,4,5)+0,G4),'[workbook]worksheet'!$O$1:$O$65536,))

 

[Carty]

Hi Special-K99

I tried both versions. The first gave the data in cell A25 of the same sheet that G4 is on, the second gave me a #value ! error. When I evaluated the second term I got a ref error after it has resolved the second mid term. It looks like this;

=Index('[workbook]worksheet!'$H$1:$H$65536,MATCH(MID(0,'[Workbook]worksheet!'$O$1:$O$65336,),)) which then goes to =Index('[workbook]worksheet!'$H$1:$H$65536,MATCH(MID(0,#ref !),)) which leads to =Index('[workbook]worksheet!'$H$1:$H$65536,MATCH(#value !,)) etc

Thank you

 

[Special-K99]

Why have you got MID(0, ???

MID(0 doesnt even appear in your original formula (nor either of mine) ?

The G4 in my formula should probably be $G$4

 

[Aladin Akyurek]

If

MID($G$4,4,5) delivers a text number, the suggestion should work:

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(IF(ISNUMBER($G$4+0),$G$4,MID($G$4,4,5))+0,[workbook]worksheet!$O$1:$O$65536,0))​

​

 

[Carty]

Why have you got MID(0, ???

MID(0 doesnt even appear in your original formula (nor either of mine) ?

The G4 in my formula should probably be $G$4

I clicked on the cell with the formula in then used "evaluate formula" in the "formulas" tab to see where the formula crashed. It resolved the Mid term down to 0, not 12345 as I was expecting

 

[Carty]

If

MID($G$4,4,5) delivers a text number, the suggestion should work:

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(IF(ISNUMBER($G$4+0),$G$4,MID($G$4,4,5))+0,[workbook]worksheet!$O$1:$O$65536,0))
​

​

Hi Aladin. This gives me the title of column H. May I try to break down your formula as I understand it?
If there is a number in G4 {(IF(ISNUMBER($G$4+0)} [p.s. I do not understand the +0 here] use the number {,$G$4,} else use the 4th to 9th characters {MID($G$4,4,5))}and turn it into a number {+0,}

 

[Carty]

If I isolate the IF term with G4 set to AB 12345CDE and then evaluate the cell it recognises that AB 12345CDE is not a number. It then strips out 12345 but displays it as "12345"+0. Evaluating this gives 0!

 

[Special-K99]

"4th to 9th characters"
Incorrect MID(G4, 4, 5) gives the 4th to 8th characaters.

"I do not understand the +0 here"
Adding +0 to a string coerces the string into a number.
So "123" is a string
"123"+0 is 123 (a number).

"12345"+0 = 0 ?
I dont see how that can occur.
The result should be 12345 (a number).

MID("AB 12345CDE", 4, 5)+0
results in 12345 (a number, I've just tested it).

Are you sure G4 contains "AB 12345CDE" ?
What's the length of G4, use =LEN(G4) in a blank cell.
The result should be 11. if it's anything else then G4 is NOT "AB 12345CDE"

 

[Aladin Akyurek]

Hi Aladin. This gives me the title of column H. May I try to break down your formula as I understand it?
If there is a number in G4 {(IF(ISNUMBER($G$4+0)} [p.s. I do not understand the +0 here] use the number {,$G$4,} else use the 4th to 9th characters {MID($G$4,4,5))}and turn it into a number {+0,}

If

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(G4,[workbook]worksheet!$O$1:$O$65536,0))

works when G4 is a number (I suppose a whole number),

=INDEX([workbook]worksheet!$H$1:$H$65536,MATCH(IF(ISNUMBER($G$4+0),$G$4,MID($G$4,4,5))+0,[workbook]worksheet!$O$1:$O$65536,0))

will also work when G4 is a number or text containing a number.