Vlookup & Lookup function error
 

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

Hi
If your Range in Sheet 2 is say, A! to B9.
Put the following formula in A2 on the first sheet:
=VLOOKUP(A1,Sheet2!A1:B9,2)

You will get an #N/A if there is no number in A1 on the first sheet.

HTH Michael

"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

Sorry
Made a typo....your range in Sheet 2 is A1 to B9
And if you want to have A2 show a blank if the data isn't available in the
table range, use the following formula
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B9,2)),"",VLOOKUP(A1 ,Sheet2!A1:B9,2))

HTH Michael

"Michael" wrote:

Hi
If your Range in Sheet 2 is say, A! to B9.
Put the following formula in A2 on the first sheet:
=VLOOKUP(A1,Sheet2!A1:B9,2)

You will get an #N/A if there is no number in A1 on the first sheet.

HTH Michael

"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

Thanks for that. It does work what you've suggested but just not with the
cell I want it to work with, that is the cell figure I want to search for on
the second sheet, first column - do you know of any reasons why this would be
happen. The cell I want to work from contains another vlookup formula.



"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

It sounds like the other VLOOKUP formula is referring to an inappropriate
reference, hence the #N/A
It might pay to supply more detail and possibly the formulae you are trying
to refer to.
I have used the VLOOKUP to read from another VLOOKUP without any problems.

Regards
Michael

Beginner" wrote:

Thanks for that. It does work what you've suggested but just not with the
cell I want it to work with, that is the cell figure I want to search for on
the second sheet, first column - do you know of any reasons why this would be
happen. The cell I want to work from contains another vlookup formula.



"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

Thanks Michael.

This is the formula I have in cell B7
=LOOKUP(D5,{0,5,10,15,20,25,30,35,40,45,50,55,60,6 5,70,75,80,85,90,95,100},{"1","1.5","2","2.5","3", "3.5","4","4.5","5","5.5","6","6.5","7","7.5","8", "8.5","9","9.5","10","10.5","11"})

I then have put in cell B12 =VLOOKUP(B7,SHEET2!A34:D54,2)

I have data in sheet 2 from A1 to D22, but only want to pick up the figure
that appears in B? which is next to the figure in A? which corresponds with
the figure showing in B7 sheet 1.

Are you able to see where I'm going wrong?

"Michael" wrote:

It sounds like the other VLOOKUP formula is referring to an inappropriate
reference, hence the #N/A
It might pay to supply more detail and possibly the formulae you are trying
to refer to.
I have used the VLOOKUP to read from another VLOOKUP without any problems.

Regards
Michael

Beginner" wrote:

Thanks for that. It does work what you've suggested but just not with the
cell I want it to work with, that is the cell figure I want to search for on
the second sheet, first column - do you know of any reasons why this would be
happen. The cell I want to work from contains another vlookup formula.



"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

So If you only have data in Cells A1 to D22 in Sheet2, why is the VLOOKUP
formula searching from A34 to D54.

Michael

"Beginner" wrote:

Thanks Michael.

This is the formula I have in cell B7
=LOOKUP(D5,{0,5,10,15,20,25,30,35,40,45,50,55,60,6 5,70,75,80,85,90,95,100},{"1","1.5","2","2.5","3", "3.5","4","4.5","5","5.5","6","6.5","7","7.5","8", "8.5","9","9.5","10","10.5","11"})

I then have put in cell B12 =VLOOKUP(B7,SHEET2!A34:D54,2)

I have data in sheet 2 from A1 to D22, but only want to pick up the figure
that appears in B? which is next to the figure in A? which corresponds with
the figure showing in B7 sheet 1.

Are you able to see where I'm going wrong?

"Michael" wrote:

It sounds like the other VLOOKUP formula is referring to an inappropriate
reference, hence the #N/A
It might pay to supply more detail and possibly the formulae you are trying
to refer to.
I have used the VLOOKUP to read from another VLOOKUP without any problems.

Regards
Michael

Beginner" wrote:

Thanks for that. It does work what you've suggested but just not with the
cell I want it to work with, that is the cell figure I want to search for on
the second sheet, first column - do you know of any reasons why this would be
happen. The cell I want to work from contains another vlookup formula.



"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

Hi

Sorry, it works if I enter in the figure from B7 but not if I ref it to the
cell B7 - is there a way around this as the figure in B7 will change & I'll
like it to carry on thru to the next formula?

"Michael" wrote:

It sounds like the other VLOOKUP formula is referring to an inappropriate
reference, hence the #N/A
It might pay to supply more detail and possibly the formulae you are trying
to refer to.
I have used the VLOOKUP to read from another VLOOKUP without any problems.

Regards
Michael

Beginner" wrote:

Thanks for that. It does work what you've suggested but just not with the
cell I want it to work with, that is the cell figure I want to search for on
the second sheet, first column - do you know of any reasons why this would be
happen. The cell I want to work from contains another vlookup formula.



"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

Sorry typo, data is in A34 to D54

"Michael" wrote:

So If you only have data in Cells A1 to D22 in Sheet2, why is the VLOOKUP
formula searching from A34 to D54.

Michael

"Beginner" wrote:

Thanks Michael.

This is the formula I have in cell B7
=LOOKUP(D5,{0,5,10,15,20,25,30,35,40,45,50,55,60,6 5,70,75,80,85,90,95,100},{"1","1.5","2","2.5","3", "3.5","4","4.5","5","5.5","6","6.5","7","7.5","8", "8.5","9","9.5","10","10.5","11"})

I then have put in cell B12 =VLOOKUP(B7,SHEET2!A34:D54,2)

I have data in sheet 2 from A1 to D22, but only want to pick up the figure
that appears in B? which is next to the figure in A? which corresponds with
the figure showing in B7 sheet 1.

Are you able to see where I'm going wrong?

"Michael" wrote:

It sounds like the other VLOOKUP formula is referring to an inappropriate
reference, hence the #N/A
It might pay to supply more detail and possibly the formulae you are trying
to refer to.
I have used the VLOOKUP to read from another VLOOKUP without any problems.

Regards
Michael

Beginner" wrote:

Thanks for that. It does work what you've suggested but just not with the
cell I want it to work with, that is the cell figure I want to search for on
the second sheet, first column - do you know of any reasons why this would be
happen. The cell I want to work from contains another vlookup formula.



"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

This is starting to get a bit messy.
I might be easier, if you want to, to email me a copy of the workbook.
It might be a lot clearer than the way we are going.
Just add a brief overview of your needs.


(Remove the CAPS)

Regards
Michael

"Beginner" wrote:

Hi

Sorry, it works if I enter in the figure from B7 but not if I ref it to the
cell B7 - is there a way around this as the figure in B7 will change & I'll
like it to carry on thru to the next formula?

"Michael" wrote:

It sounds like the other VLOOKUP formula is referring to an inappropriate
reference, hence the #N/A
It might pay to supply more detail and possibly the formulae you are trying
to refer to.
I have used the VLOOKUP to read from another VLOOKUP without any problems.

Regards
Michael

Beginner" wrote:

Thanks for that. It does work what you've suggested but just not with the
cell I want it to work with, that is the cell figure I want to search for on
the second sheet, first column - do you know of any reasons why this would be
happen. The cell I want to work from contains another vlookup formula.



"Beginner" wrote:

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.