Vlookup multiple criteria

[se7098]

A B
Hispanic or Latino White
Not Hispanic or Latino African American
Not Hispanic or Latino Asian
Hispanic or Latino Afrian American

If a = Hispanic or Latino and b= white return value of Hispanic or Latino
If a = not Hispanic or Latino and b=white return value of white.
If a = Hispanic or Latino and b = African American return value of Hispanic
or Latino (all other races)
If a=not Hispanic or Latino and b=African American return value of African
American

Can this be done with a formula? Thanks.

 

[NBVC]

Try


Code
-------------------
=IF(A1="","",IF(A1="Hispanic or Latino",IF(B1="White","Hispanic or Latino","Hispanic or Latino (all other races)"),IF(B1="White","White","African American"))
-------------------

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[se7098]

that seems to be getting me close...

however, in the situation where a=not hispanic or latino and b=asian i am
receiving a value of african american.

so what i need is to understand exactly what is happening within the formula
so i can know where to make adjustments

thanks!

 

[NBVC]

Are there many more combinations? If so, then probably best to create
table on the side listing the A possibilities (e.g. in X1:X100) and th
B possibilities (e.g in Y1:Y100) along with the desired results (e.g i
Z1:Z100).

then apply formula

=Lookup(2,1/((X1:X100=A1)*(Y1:Y100=B1)),Z1:Z100)

this assumes table is in X1:Z100.. adjust as necessary

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Eduardo]

Hi,
try

=IF(AND(A1="Hispanic or Latino",A1="White"),"Hispanic or
Latino",IF(AND(A1="Not Hispanic or
Latino",A1="White"),"White",IF(AND(A1="Hispanic or Latino",A1="African
American"),"Hispanic or Latino",IF(AND(A1="Not Hispanic or Latino",A1=African
American),"African American",""))))

 

[NBVC]

Hi,
try

=IF(AND(A1="Hispanic or Latino",A1="White"),"Hispanic or
Latino",IF(AND(A1="Not Hispanic or
Latino",A1="White"),"White",IF(AND(A1="Hispanic or Latino",A1="African
American"),"Hispanic or Latino",IF(AND(A1="Not Hispanic or
Latino",A1=African
American),"African American",""))))

I think that is where I was getting at !!!!!! but the Lookup is
better(easier to maintain and is cleaner)... wko


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)

 

[se7098]

you, my friend, are a genius...rock star of immense proportions! thank you
so very much!!!

 

[catts22]

Hi Can a Vlookup be done with 3 criteria?

I have this:

I have 3 columns on Sheet 1 and need to have the result in the fourth column
based on information on Sheet 2.

Sheet 1
A B C D
1 State City Address Company
2 AL Birming ALL
3 NC All ALL
4 NY New York 123 X St
5 NY Long City 999 A St

Sheet 2
A B C D
1 State City Address Company
2 AL Birming ALL Yellow
3 NC All ALL Yellow
4 NY New York 123 X St Red
5 NY Long City 999 A St Blue
6 CT ALL ALL Red
7 GA ATLANTA 5TH St Red

In D2 on Sheet 1 - I tried this formula with the idea that I need the
company name shown on column D of Sheet 2 if Column A,B,C match on both sheets

=INDEX('Sheet 2'!D2101,MATCH(1,(a2='Sheet 2'!A2:A101)*(b2='Sheet
2'!B2:B101)*(c2='Sheet 2'!C2:C101),0))

I keep getting # N/A - I took all formating out and on both sheets and made
sure there were no extra spaces etc.

On this formula I don't understand what the the 1 refers to after Match(1 -
I copied this formula from another post and edited it.

Thanks

 

[NBVC]

You should really start your own thread as this is not even related to
the question/answer given in this thread...

... anyways... the 1 is what we need to match in the resultant array
when you multiply the results of the three separate conditions.

Each condition will give TRUE/FALSE results when you multiply TRUE*TRUE
you get 1, any other combo gives 0... so the Match(1,...) looks for when
you are in escence multiplying a TRUE*TRUE*TRUE (ie. when all 3
conditions positively coincide)... and then the position nubmer is
returned and indexed on the Index Range...

.. Your formula looks ok. Did you confirm with CTRL+Shift+Enter and
not just ENTER? To get #N/A otherwise means there is something still
not exactly right where you think there should be a match.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)