Hi,

I'm having trouble getting this to work.

I have on sheet 1 an IF function which assign a value between 1 to 5 dependant on criteria.

On sheet 2 I want to lookup this value against my lookup table (hr) and return the value in column 2, hence

=VLOOKUP('Sheet 1'!W37,hr,2,FALSE)

But I keep getting #N/A.

Can anyone help?

Hi,

i've even tried =IF() with this and it still wont work. Has this got something to do with the original If function. Can't I compare this to anything else?

How is the cell formatted with the IF statement on Sheet1? Also, it might be that your criteria are not correct. Can you post the entire IF statement?

It might be that the comparison data are not the same type. Meaning one is text and the other numeric. I usually do a test with
istext(cell range)

do this on both the bolded areas in question.
=VLOOKUP('Sheet 1'!W37 ,hr,2,FALSE)


HTH
texasalynn

Just at a glance, the default name of the first sheet doesn't have a space between Sheet and 1. Also, I don't think the single quotes should be there.

Try:

=VLOOKUP(Sheet1!W37,hr,2,FALSE)

If the space is in the name, then the single quote is needed.