Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP query
hello gods of excel,
I am trying create a vlookup query where by if data is matching in column a1 on worksheet1 with column a1 on worksheet2, it then looks to see if column b2 on worksheet1 is matching with column b2 on worksheet2 then returns a "match" or "not matching" value as text. I can perform this on one column but not sure how to fiddle with the formula to expand it too search the second column. I'm using: =IF(ISNA(VLOOKUP(Sheet1!$A$2:$A$14,Sheet2!$A$2:$A$ 14,1,0)),"not matching","matching") any tips will be greatly appreciated, thanks, Clive |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP query
=IF(ISNUMBER(MATCH(Sheet1!A2,Sheet2!$A$2:$A$14,0)) ,IF(ISNUMBER(MATCH(Sheet1!A2,Sheet2!$A$2:$A$14,0)) ,"match","no
match")) HTH Kostis Vezerides wrote: hello gods of excel, I am trying create a vlookup query where by if data is matching in column a1 on worksheet1 with column a1 on worksheet2, it then looks to see if column b2 on worksheet1 is matching with column b2 on worksheet2 then returns a "match" or "not matching" value as text. I can perform this on one column but not sure how to fiddle with the formula to expand it too search the second column. I'm using: =IF(ISNA(VLOOKUP(Sheet1!$A$2:$A$14,Sheet2!$A$2:$A$ 14,1,0)),"not matching","matching") any tips will be greatly appreciated, thanks, Clive |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP query
This is quite simple. Copy you formula to the cell and which you want your
answer to be displayed and then, Change you formula cell references to B, as follows: =IF(ISNA(VLOOKUP(Sheet1!$B$2:$B$14,Sheet2!$B$2:$B$ 14,1,0)),"not matching","matching") If you want this to change automatically then remove the $ sign from the fomula so the reference cahnges, this work well when you are copying this formula down rows, If you do this then it should look like this: =IF(ISNA(VLOOKUP(Sheet1!$B2:$B14,Sheet2!$B2:$B14,1 ,0)),"not matching","matching") " wrote: hello gods of excel, I am trying create a vlookup query where by if data is matching in column a1 on worksheet1 with column a1 on worksheet2, it then looks to see if column b2 on worksheet1 is matching with column b2 on worksheet2 then returns a "match" or "not matching" value as text. I can perform this on one column but not sure how to fiddle with the formula to expand it too search the second column. I'm using: =IF(ISNA(VLOOKUP(Sheet1!$A$2:$A$14,Sheet2!$A$2:$A$ 14,1,0)),"not matching","matching") any tips will be greatly appreciated, thanks, Clive |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP query
thank you, but unfortunately that only seems to return a match if the
value in the column b is presernt anywhere in the range of column b on worksheet2. here is some sample data: Name Application G.Anderson VB6 G.Anderson Team Spirit B.Jones MapManager/Editor A.Smith Groupwise 5.5 assuming name is column A and Application is column B. these are like records so to speak. So where a name appears in column A on worksheet one I need to check to see if it appears in worksheet two. And if it does then see if one of the application name's that is next to it in worksheet 1 column b also appears next to it on worksheet 2 column b. Been trying to do this for 4 days now and all i get is errors :'(. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP query
haha no it seems to match 1 or two then returns no match for everything
else. thank you for your suggestions. I think if i appraoch it as comparing records, using name as a unique identifier then checking to see what are the unique iterations of it between the two worksheets. I'll get there in the end I am hoping. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP query
Ok I have sort of solved this problem and thought I would share the
love so to speak. What I did eventually was merge the 2 datain the columns in worksheet1 in the next column using something like =A2&" "&B2 if you don't want spaces use =A2&B2 I did the same on the worksheet two and then used this formula to look for duplicates =IF(ISNA(VLOOKUP(C2,Sheet4!$C$2:$C$14,1,0)),"no match", "match") hope this helps others. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup problem with Access Query import into Excel 2000 | New Users to Excel | |||
VLOOKUP QUERY | Excel Discussion (Misc queries) | |||
VLOOKUP Query | Excel Worksheet Functions | |||
VLookup query | Excel Discussion (Misc queries) | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions |