 # vlook up & if isnumber issue

• Hi guys hope you are all well,

I have a columns witch consist of numbers and letters ie column A1 value is 2c.

I want to be able to count the numbers and ignore the letter.

I been trying to adjust my current formula to fit a if isnumer, with no luck my formula is: =IFERROR(VLOOKUP(\$D\$14,Table57,3,FALSE),"")

how do I adjust it to accommodate the if isnumber part?

• Re: vlook up &amp; if isnumber issue

Is there only one number preceding the text?

Windy

• Re: vlook up &amp; if isnumber issue

Quote from azumi;736541

Maybe:

=IFERROR(VLOOKUP(LEFT(\$D\$14,1),Table57,3,FALSE),"")

thanks will try this one out.

• Re: vlook up &amp; if isnumber issue

Quote from Windy58;736542

Is there only one number preceding the text?

Windy

Hi, yes its one number and one letter.

• Re: vlook up &amp; if isnumber issue

Quote from azumi;736541

Maybe:

=IFERROR(VLOOKUP(LEFT(\$D\$14,1),Table57,3,FALSE),"")

Just tried it, not getting anything show up in the columns just blank.

• Re: vlook up &amp; if isnumber issue

You might need to coerce the LEFT() result to a number:

[COLOR="#0000FF"]=IFERROR(VLOOKUP(LEFT(\$D\$14,1)[COLOR="#FF0000"]+0[/COLOR],Table57,3,FALSE),"") [/COLOR]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: vlook up &amp; if isnumber issue

Quote from NBVC;736547

You might need to coerce the LEFT() result to a number:

=IFERROR(VLOOKUP(LEFT(\$D\$14,1)+0,Table57,3,FALSE),"")

Hi, thanks. just tried the above and is returning a blank.

• Re: vlook up &amp; if isnumber issue

Are you sure the first column of table Table57 contains single digit numbers?

If yes, post a sample workbook.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: vlook up &amp; if isnumber issue

Quote from NBVC;736567

Are you sure the first column of table Table57 contains single digit numbers?

If yes, post a sample workbook.

Sorry my bad. Its column d in table 57 which contains a digit and a letter. The table starts from column B and ends at column P.

The code will be entered in a separate worksheet in column G iirc

I'm back in the office Monday so can confirm the above then

• Re: vlook up &amp; if isnumber issue

You will have to make sure that your reference table in the VLOOKUP starts at column D then...

Or, alternatively, you can try INDEX/MATCH

e.g.

[COLOR="#0000FF"]=IFERROR(INDEX(INDEX(Table57,0,[COLOR="#0000FF"][COLOR="#FF0000"]3[/COLOR][/COLOR]),MATCH(LEFT(\$D\$14,1)+0,INDEX(Table57,0,[COLOR="#FF0000"]4[/COLOR]),0)),"")[/COLOR]

The Red 3 represents column D (which is third column in the table57) and the the 4 assumes you want to pull from column E (the fourth column - change to suit)

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: vlook up &amp; if isnumber issue

Morning,

Just tried the above but doesn't seem to want to work for me ...

iv added a link to my workbook, could you let me know where im going wrong.

Many Thanks

https://www.dropbox.com/s/g353fsj1f2lufk1/test.xlsm?dl=0

The formula is in the Dashboard worksheet in column F15,F17 and F19

• Re: vlook up &amp; if isnumber issue

From the start you mentioned your were looking up part of a cell, this ccm01 is matching the whole text string....

Try:

[COLOR="#0000FF"]=IFERROR(INDEX(INDEX(Table57,0,3),MATCH(D14,INDEX(Table57,0,1),0)),"")[/COLOR]

or
[COLOR="#0000FF"]
=IFERROR(VLOOKUP(D14,Table57,3,0),"")[/COLOR]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: vlook up &amp; if isnumber issue

Thanks for getting back to me.

=IFERROR(INDEX(INDEX(Table57,0,3),MATCH(D14,INDEX(Table57,0,1),0)),"")

the above works, the issue im having is not being able to reflect the number in the respective graphs.

I think this was due to there being a letter in the cell.

any ideas?

Thanks again

• Re: vlook up &amp; if isnumber issue

Ok. I understand now. You want the number part of the result... not to lookup a number part of a mixed string.....

Try then:

[COLOR="#0000FF"]=IFERROR(LEFT(INDEX(INDEX(Table57,0,3),MATCH(D14,INDEX(Table57,0,1),0)),1)+0,"")[/COLOR]

or

[COLOR="#0000FF"]=IFERROR(LEFT(VLOOKUP(D14,Table57,3,0),1)+0,"")[/COLOR]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: vlook up &amp; if isnumber issue

Both of the above are returning back "FALSE"

• Re: vlook up &amp; if isnumber issue

I've tried both of them again, and they both return a 2.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: vlook up &amp; if isnumber issue

Thanks

• Re: vlook up &amp; if isnumber issue

Hi, Thanks it seems to have sorted it self out appreciate all the help.

Is there a way to show both the number and letter but only count the number?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!