VLookup to find two values first
 

Hello,

I am creating a list where I want Vlookup to populate by returning
particular values from an Overview list. However, I want Vlookup, to
find (match) the values in two different cells before donig so:

e.g. My overview list contains the last names and first names in
seperate cells:

A B C
Smith John Red
Smith Tom Blue

I want Vlookup to lookup the values in cells A and B before giving me
the value in C.

I am using the following formula:

=IF((AND(A1=Overview!A1,B1=Overview!B1)),VLOOKUP($ A:$A,Overview!$A:$C,
3,FALSE)," ")

However, the problem is, that the names can be anywhere in the
Overview list and therefore I cannot use absolute cell references.
E.g. Smith, John can be in cell A5 in the Overview list and not
necessarily A1.

Does anyone know, a better way of using this formula.

Thanks a ton!

VLookup to find two values first
 

One way ..

In your other sheet,
assuming you have the last names and first names running in A2 and B2 down,
you could drop this in C2's formula bar,
then array-enter the formula by pressing Ctrl+Shift+Enter:

=IF(ISNA(MATCH(1,(A2=Overview!A$2:A$100)*(B2=Overv iew!B$2:B$100),0)),"",INDEX(Overview!C$2:C$100,MAT CH(1,(A2=Overview!A$2:A$100)*(B2=Overview!B$2:B$10 0),0)))

Copy C2 down as far as required. Col C will return the results from col C in
Overview. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
Hello,

I am creating a list where I want Vlookup to populate by returning
particular values from an Overview list. However, I want Vlookup, to
find (match) the values in two different cells before donig so:

e.g. My overview list contains the last names and first names in
seperate cells:

A B C
Smith John Red
Smith Tom Blue

I want Vlookup to lookup the values in cells A and B before giving me
the value in C.

I am using the following formula:

=IF((AND(A1=Overview!A1,B1=Overview!B1)),VLOOKUP($ A:$A,Overview!$A:$C,
3,FALSE)," ")

However, the problem is, that the names can be anywhere in the
Overview list and therefore I cannot use absolute cell references.
E.g. Smith, John can be in cell A5 in the Overview list and not
necessarily A1.

Does anyone know, a better way of using this formula.

Thanks a ton!

VLookup to find two values first
 

On Sep 10, 11:17 am, "Max" wrote:
One way ..

In your other sheet,
assuming you have the last names and first names running in A2 and B2 down,
you could drop this in C2's formula bar,
then array-enter the formula by pressing Ctrl+Shift+Enter:

=IF(ISNA(MATCH(1,(A2=Overview!A$2:A$100)*(B2=Overv iew!B$2:B$100),0)),"",IND*EX(Overview!C$2:C$100,MA TCH(1,(A2=Overview!A$2:A$100)*(B2=Overview!B$2:B$1 0*0),0)))

Copy C2 down as far as required. Col C will return the results from col C in
Overview. Adapt the ranges to suit.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
wrote in message

oups.com...



Hello,

I am creating a list where I want Vlookup to populate by returning
particular values from an Overview list. However, I want Vlookup, to
find (match) the values in two different cells before donig so:

e.g. My overview list contains the last names and first names in
seperate cells:

A B C
Smith John Red
Smith Tom Blue

I want Vlookup to lookup the values in cells A and B before giving me
the value in C.

I am using the following formula:

=IF((AND(A1=Overview!A1,B1=Overview!B1)),VLOOKUP($ A:$A,Overview!$A:$C,
3,FALSE)," ")

However, the problem is, that the names can be anywhere in the
Overview list and therefore I cannot use absolute cell references.
E.g. Smith, John can be in cell A5 in the Overview list and not
necessarily A1.

Does anyone know, a better way of using this formula.

Thanks a ton!- Hide quoted text -

- Show quoted text -

Hi Max,

Thanks! That worked!
But is there a way to make the cells remain blank if they are blank in
the Overview sheet, currently it adds a 0.

Thanks again!

VLookup to find two values first
 

But is there a way to make the cells remain blank if they are blank in
the Overview sheet, currently it adds a 0.

You could use this instead in C2, array-entered:
=IF(ISNA(MATCH(1,(A2=Overview!A$2:A$100)*(B2=Overv iew!B$2:B$100),0)),"",IF(INDEX(Overview!C$2:C$100, MATCH(1,(A2=Overview!A$2:A$100)*(B2=Overview!B$2:B $100),0))=0,"",INDEX(Overview!C$2:C$100,MATCH(1,(A 2=Overview!A$2:A$100)*(B2=Overview!B$2:B$100),0))) )

Another way to consider, perhaps simpler? is just switch off the display of
zeros in the sheet via clicking Tools Options View tab. Uncheck "Zero
values" OK.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote
Hi Max,

Thanks! That worked!

But is there a way to make the cells remain blank if they are blank in
the Overview sheet, currently it adds a 0.

Thanks again!

VLookup to find two values first
 

You can use a trick
If your data is:
A B C
Smith John Red
Smith Clein Green

Add one more column before column A then first name column will become
column B and type
=B1&C1 and do the same with the name you are searching for and then Vlookup
that value in this database.
=vlookup(lookvalue, A1:D10, 4, 0) It might help you.


"Max" wrote:

But is there a way to make the cells remain blank if they are blank in
the Overview sheet, currently it adds a 0.

You could use this instead in C2, array-entered:
=IF(ISNA(MATCH(1,(A2=Overview!A$2:A$100)*(B2=Overv iew!B$2:B$100),0)),"",IF(INDEX(Overview!C$2:C$100, MATCH(1,(A2=Overview!A$2:A$100)*(B2=Overview!B$2:B $100),0))=0,"",INDEX(Overview!C$2:C$100,MATCH(1,(A 2=Overview!A$2:A$100)*(B2=Overview!B$2:B$100),0))) )

Another way to consider, perhaps simpler? is just switch off the display of
zeros in the sheet via clicking Tools Options View tab. Uncheck "Zero
values" OK.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote
Hi Max,

Thanks! That worked!

But is there a way to make the cells remain blank if they are blank in
the Overview sheet, currently it adds a 0.

Thanks again!

VLookup to find two values first
 

=INDEX(Colors,Match(1,(Names="Smith ")*(FirstNames="Tom"),0))
Validate wirth Ma+Ctrl+enter

http://cjoint.com/?jpl2gwmVEn

JB
http://boisgontierjacques.free.fr

On 10 sep, 02:44, wrote:
Hello,

I am creating a list where I want Vlookup to populate by returning
particular values from an Overview list. However, I want Vlookup, to
find (match) the values in two different cells before donig so:

e.g. My overview list contains the last names and first names in
seperate cells:

A B C
Smith John Red
Smith Tom Blue

I want Vlookup to lookup the values in cells A and B before giving me
the value in C.

I am using the following formula:

=IF((AND(A1=Overview!A1,B1=Overview!B1)),VLOOKUP($ A:$A,Overview!$A:$C,
3,FALSE)," ")

However, the problem is, that the names can be anywhere in the
Overview list and therefore I cannot use absolute cell references.
E.g. Smith, John can be in cell A5 in the Overview list and not
necessarily A1.

Does anyone know, a better way of using this formula.

Thanks a ton!