Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Syntax Error | New Users to Excel | |||
Vlookup #N/A error due to formatting | Excel Worksheet Functions | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |