VLookup to find two values first

[roniaelm]

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!

 

[Max]

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=Overview!B$2:B$100),0)),"",INDEX(Overview!C$2:C$100,MATCH(1,(A2=Overview!A$2:A$100)*(B2=Overview!B$2:B$100),0)))

Copy C2 down as far as required. Col C will return the results from col C in
Overview. Adapt the ranges to suit.

 

[roniaelm]

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=Overview!B$2:B$100),0)),"",IND­EX(Overview!C$2:C$100,MATCH(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 Cin
Overview. Adapt the ranges to suit.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik














- 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!

 

[Max]

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=Overview!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,(A2=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.

---
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!

 

[Guest]

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, A110, 4, 0) It might help you.

 

[JB]

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

http://cjoint.com/?jpl2gwmVEn

JB
http://boisgontierjacques.free.fr