Vlook up 2 sheets

  • Thread starter BNT1 via OfficeKB.com
  • Start date
B

BNT1 via OfficeKB.com

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
 
D

Dave Peterson

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)),"",vlookup(...,sheet1!...,false))
in one column
and then:
=if(iserror(vlookup(...,sheet2!...,false)),"",vlookup(...,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.)
 
B

BNT1 via OfficeKB.com

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 said:
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)),"",vlookup(...,sheet1!...,false))
in one column
and then:
=if(iserror(vlookup(...,sheet2!...,false)),"",vlookup(...,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.)
[quoted text clipped - 22 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top