Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlook up 2 sheets
Hi
Have used the following formula extracted from this site for some time =IF(A2="","",IF(NOT(ISERROR(VLOOKUP(A2,UK!$A$2:$K$ 10000,4,FALSE))),VLOOKUP(A2, UK!$A$2:$K$10000,4,FALSE),VLOOKUP(A2,Export!$A$2:$ K$10000,4,FALSE))) Works fine, except that there are duplicates on the sheets and often it returns the one I didn't want. Is it possible, someone can help to amend the formula, so that it returns the greatest value in column 4 on the sheets UK and Export for the two entries Example LD123456 - 5700 LD123456 - 5740 Vlookup to return the greater value , ie, 5740. Thanks in anticipation Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200909/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlook up 2 sheets
There's duplicates across the sheets or within a sheet?
I'm guessing that you mean across the sheets. With formulas like that, I'll use multiple columns (and hide the columns if I don't want to see them). =if(iserror(vlookup(...,sheet1!...,false)),"",vloo kup(...,sheet1!...,false)) in one column and then: =if(iserror(vlookup(...,sheet2!...,false)),"",vloo kup(...,sheet2!...,false)) Then in the third column, I'll check to see what I found. =if(count(x1:y1)=0,"No match on either sheet",max(x1:y1)) (I figured you were retrieving numbers, so I could use "" without worry.) "BNT1 via OfficeKB.com" wrote: Hi Have used the following formula extracted from this site for some time =IF(A2="","",IF(NOT(ISERROR(VLOOKUP(A2,UK!$A$2:$K$ 10000,4,FALSE))),VLOOKUP(A2, UK!$A$2:$K$10000,4,FALSE),VLOOKUP(A2,Export!$A$2:$ K$10000,4,FALSE))) Works fine, except that there are duplicates on the sheets and often it returns the one I didn't want. Is it possible, someone can help to amend the formula, so that it returns the greatest value in column 4 on the sheets UK and Export for the two entries Example LD123456 - 5700 LD123456 - 5740 Vlookup to return the greater value , ie, 5740. Thanks in anticipation Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200909/1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlook up 2 sheets
Thanks Dave
This looks like one solution which I did not think of. It will mean having to record all my macros again. Will give this a whirl Regards Dave Peterson wrote: There's duplicates across the sheets or within a sheet? I'm guessing that you mean across the sheets. With formulas like that, I'll use multiple columns (and hide the columns if I don't want to see them). =if(iserror(vlookup(...,sheet1!...,false)),"",vlo okup(...,sheet1!...,false)) in one column and then: =if(iserror(vlookup(...,sheet2!...,false)),"",vlo okup(...,sheet2!...,false)) Then in the third column, I'll check to see what I found. =if(count(x1:y1)=0,"No match on either sheet",max(x1:y1)) (I figured you were retrieving numbers, so I could use "" without worry.) Hi [quoted text clipped - 22 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200909/1 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlook UP | Excel Discussion (Misc queries) | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
Need Help about vlook | Excel Worksheet Functions | |||
Vlook up for matching data in two seperate sheets | Excel Discussion (Misc queries) |