Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I even using the right function?
Hi
I am trying to figure out if I can use VLOOKUP to check a range of cells. I am probably using the wrong fuction and there is much simpler way of doing this - I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to A20 (i've called the table array masters!) and if they do I want them to put this same value into I3. I hope that makes sense! Can anyone please tell me if this is at all possible and if so how I do it? Thank you very much |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I even using the right function?
What happens if more than one of the values in G2:J2 match with the
values in A13:A20 of sheet 3 ? Look at using the MATCH function in Excel Help - you will probably have to use this in conjunction with ISNA to trap errors which will occur if there is no match. Hope this helps. Pete On Apr 26, 11:06 pm, Be18 wrote: Hi I am trying to figure out if I can use VLOOKUP to check a range of cells. I am probably using the wrong fuction and there is much simpler way of doing this - I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to A20 (i've called the table array masters!) and if they do I want them to put this same value into I3. I hope that makes sense! Can anyone please tell me if this is at all possible and if so how I do it? Thank you very much |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I even using the right function?
Actually, I think you want HLOOKUP.
=HLOOKUP(I3,'Sheet2!G2:J2,1,FALSE) HTH, Barb Reinhardt "Be18" wrote: Hi I am trying to figure out if I can use VLOOKUP to check a range of cells. I am probably using the wrong fuction and there is much simpler way of doing this - I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to A20 (i've called the table array masters!) and if they do I want them to put this same value into I3. I hope that makes sense! Can anyone please tell me if this is at all possible and if so how I do it? Thank you very much |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I even using the right function?
Thank you for your responses!!! However this doesn't seem to work! Maybe
because there's no mention of sheet 3!! Am I trying to do too much maybe? "Barb Reinhardt" wrote: Actually, I think you want HLOOKUP. =HLOOKUP(I3,'Sheet2!G2:J2,1,FALSE) HTH, Barb Reinhardt "Be18" wrote: Hi I am trying to figure out if I can use VLOOKUP to check a range of cells. I am probably using the wrong fuction and there is much simpler way of doing this - I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to A20 (i've called the table array masters!) and if they do I want them to put this same value into I3. I hope that makes sense! Can anyone please tell me if this is at all possible and if so how I do it? Thank you very much |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I even using the right function?
Thank you for your replies! There definately won't be more than one value
matching A13:A20! I've tried matching but I don't seem to be able to get this to put the same in text it I3 that matches! If that makes sense??!! Maybe it is too difficult a formula? "Pete_UK" wrote: What happens if more than one of the values in G2:J2 match with the values in A13:A20 of sheet 3 ? Look at using the MATCH function in Excel Help - you will probably have to use this in conjunction with ISNA to trap errors which will occur if there is no match. Hope this helps. Pete On Apr 26, 11:06 pm, Be18 wrote: Hi I am trying to figure out if I can use VLOOKUP to check a range of cells. I am probably using the wrong fuction and there is much simpler way of doing this - I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to A20 (i've called the table array masters!) and if they do I want them to put this same value into I3. I hope that makes sense! Can anyone please tell me if this is at all possible and if so how I do it? Thank you very much |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I even using the right function?
MATCH will help you to find if there is a match, but you need to use
INDEX to return the value that matches. I've tested this formula out on some sample data and it works, though it is a bit of a nightma =IF(MAX(IF(ISNA(MATCH(Sheet2!G$2,Sheet3!$A$13:$A$2 0,0)),0,MATCH(Sheet2! G$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!H$ 2,Sheet3!$A$13:$A $20,0)),0,MATCH(Sheet2!H$2,Sheet3!$A$13:$A$20,0)), IF(ISNA(MATCH(Sheet2! I$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!I$2,Shee t3!$A$13:$A $20,0)),IF(ISNA(MATCH(Sheet2!J$2,Sheet3!$A$13:$A$2 0,0)),0,MATCH(Sheet2! J$2,Sheet3!$A$13:$A$20,0)))=0,0,INDEX(Sheet3!$A$13 :$A $20,MAX(IF(ISNA(MATCH(Sheet2!G$2,Sheet3!$A$13:$A$2 0,0)),0,MATCH(Sheet2! G$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!H$ 2,Sheet3!$A$13:$A $20,0)),0,MATCH(Sheet2!H$2,Sheet3!$A$13:$A$20,0)), IF(ISNA(MATCH(Sheet2! I$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!I$2,Shee t3!$A$13:$A $20,0)),IF(ISNA(MATCH(Sheet2!J$2,Sheet3!$A$13:$A$2 0,0)),0,MATCH(Sheet2! J$2,Sheet3!$A$13:$A$20,0))))) Perhaps an array function would have been shorter. This is all one formula - watch out for line-breaks. This returns 0 if there is no match, otherwise it returns the value from A13:A20 of Sheet3 which matches any of the entries is G2:J2 of Sheet2. If you are not too bothered about an error being returned in cell I3, then this shorter version will return #VALUE if there is no match: =INDEX(Sheet3!$A$13:$A$20,MAX(IF(ISNA(MATCH(Sheet2 !G$2,Sheet3!$A$13:$A $20,0)),0,MATCH(Sheet2!G$2,Sheet3!$A$13:$A$20,0)), IF(ISNA(MATCH(Sheet2! H$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!H$2,Shee t3!$A$13:$A $20,0)),IF(ISNA(MATCH(Sheet2!I$2,Sheet3!$A$13:$A$2 0,0)),0,MATCH(Sheet2! I$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!J$ 2,Sheet3!$A$13:$A $20,0)),0,MATCH(Sheet2!J$2,Sheet3!$A$13:$A$20,0))) ) You could always trap the error in another cell using IF(ISERROR(I3) etc... Hope this helps. Pete On Apr 27, 2:16 am, Be18 wrote: Thank you for your replies! There definately won't be more than one value matching A13:A20! I've tried matching but I don't seem to be able to get this to put the same in text it I3 that matches! If that makes sense??!! Maybe it is too difficult a formula? "Pete_UK" wrote: What happens if more than one of the values in G2:J2 match with the values in A13:A20 of sheet 3 ? Look at using the MATCH function in Excel Help - you will probably have to use this in conjunction with ISNA to trap errors which will occur if there is no match. Hope this helps. Pete On Apr 26, 11:06 pm, Be18 wrote: Hi I am trying to figure out if I can use VLOOKUP to check a range of cells. I am probably using the wrong fuction and there is much simpler way of doing this - I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to A20 (i've called the table array masters!) and if they do I want them to put this same value into I3. I hope that makes sense! Can anyone please tell me if this is at all possible and if so how I do it? Thank you very much- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Am I even using the right function?
Give us one specific problem to solve. The 3 sheets in I3 lost me.
"Be18" wrote: Thank you for your responses!!! However this doesn't seem to work! Maybe because there's no mention of sheet 3!! Am I trying to do too much maybe? "Barb Reinhardt" wrote: Actually, I think you want HLOOKUP. =HLOOKUP(I3,'Sheet2!G2:J2,1,FALSE) HTH, Barb Reinhardt "Be18" wrote: Hi I am trying to figure out if I can use VLOOKUP to check a range of cells. I am probably using the wrong fuction and there is much simpler way of doing this - I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to A20 (i've called the table array masters!) and if they do I want them to put this same value into I3. I hope that makes sense! Can anyone please tell me if this is at all possible and if so how I do it? Thank you very much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |