ISnumber with Index/Match GoogleSheets

[shlomek12]

Can someone please help me, what is wrong here?

if(ISNUMBER(A68),INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2),INDEX('Pricing & Availability'!A:E,match(A68,'Pricing & Availability'!A:A,0),2))

I am getting the massage:

"Error Did not find value '9178891612' in MATCH evaluation."

A68 is indeed a number


Thanks in advance

 

[shlomek12]

Re: ISnumber with Index/Match

Bump

 

[gaz_chops]

Re: ISnumber with Index/Match

If it is a number, why do you need A68*1?

Is it a number or text in 'Pricing & Availability'!A:A?

 

[Fluff]

Re: ISnumber with Index/Match

Looks like you have the formula the wrong way round, I suspect it should be
if(ISNUMBER(A68),INDEX('Pricing & Availability'!A:E,match(A68,'Pricing & Availability'!A:A,0),2),INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2))

Alternatively, you could just use
INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2)

 

[gaz_chops]

Re: ISnumber with Index/Match

Alternatively, you could just use
INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2)

Will that work if A:A is text?

 

[shlomek12]

Re: ISnumber with Index/Match

.

 

[shlomek12]

Re: ISnumber with Index/Match

Looks like you have the formula the wrong way round, I suspect it should be
if(ISNUMBER(A68),INDEX('Pricing & Availability'!A:E,match(A68,'Pricing & Availability'!A:A,0),2),INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2))

Alternatively, you could just use
INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2)

I am getting the error:


"Function MULTIPLY parameter 1 expects number values. But 'B01BV2CC56' is a text and cannot be coerced to a number."


Sometimes A68 will be a Text cell and sometimes it will be a number cell, this is the reason why I need a "IF statement"

 

[Fluff]

Re: ISnumber with Index/Match

Is this in Excel or some other spreadsheet software?

 

[gaz_chops]

Re: ISnumber with Index/Match

If you have a value of, say 1234567 is that showing as a value or text in 'Pricing & Availability'!A:A?

 

[shlomek12]

Re: ISnumber with Index/Match

Is this in Excel or some other spreadsheet software?

Google Sheet