ISNUMBER,VLOOKUP,IFERROR

[Lostatwork]

Hi all,

I'm quite new to excel and learning on the job,

I have a spreadsheet I manage and am trying to do several things,

The columns i am trying to change are the tracking sheet and there are 2 information sheets "sheet1" and "sheet2", I am currently using the following formula

=ISNUMBER(VLOOKUP($A4,'Sheet2'!$A:$A,$H$1,FALSE))

currently anything that is on sheet 2 still is returning TRUE as it should but the formula returns FALSE for anything on Sheet1 as its not looking there

the issue is column A has a mixture of info from "Sheet1" and "Sheet2" and anything that is on sheet 1 is returning as false

Column A has number in it, either 5 digit number or 7 digit numbers

im trying to get a formula to vlookup anything greater than 99999 in sheet2 and anything less in sheet1 and still return the ISNUMBER true or false

any assistance would be greatly appreciated and any further info can be provided on request.


regards

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ISNUMBER(XMATCH($A4,VSTACK(Sheet1!$A$2:$A$1000,Sheet2!$A$2:$A$1000)))

 

[Lostatwork]

Hi & welcome to MrExcel.
How about

=ISNUMBER(XMATCH($A4,VSTACK(Sheet1!$A$2:$A$1000,Sheet2!$A$2:$A$1000)))

Hmm when i copy this into place it doesnt seem to work it copies the whole like in the cell and doesnt act like a formula

 

[Joe4]

Hmm when i copy this into place it doesnt seem to work it copies the whole like in the cell and doesnt act like a formula

It sounds like you have pre-formatted the cell as Text, so instead of entering a formula, you are entering literal text.
Try changing the format of that cell to General first, then enter the formula.

 

[Fluff]

Do you mean that you just see the formula in the cell?