Two possible values with a lookup?
 

Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have the
ability to choose either X or R in Column B. Some times Column A has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show up. If I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

If there are *only the 2 possibilities* you can use the lookup for the 1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have the
ability to choose either X or R in Column B. Some times Column A has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show up. If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

Hi,

In one cell use

=VLOOKUP(A9,A1:B4,2,)

Then the following will return the second one even if they are both X or
both R.

=VLOOKUP(A10,INDIRECT("A"&MATCH(A10,A1:A4,0)+1&":B 4"),2,0)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Homer" wrote:

Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have the
ability to choose either X or R in Column B. Some times Column A has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show up. If I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

Thanks for the help.

What would you do if there were three or more possibilities? Would you just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup for the 1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have the
ability to choose either X or R in Column B. Some times Column A has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show up. If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

You would need a more complicated formula. How would you want the results
displayed:

98765...result1...result2...result3

Or

98765...result1
..............result2
..............result3

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Thanks for the help.

What would you do if there were three or more possibilities? Would you
just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup for the
1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have
the
ability to choose either X or R in Column B. Some times Column A has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show up.
If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

I was trying to cover a few different scenarios with one question. Your
original answer took care of most. For the complicated one I will use
hlookup. Here is an example of the complicated one:

74019 74019 39008 39008 69861 74831 74831 74831
row of cells with data for other uses
row of cells with data for other uses
3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4"
empty cell
1,000 5,000 600 800 3,000 50 300
0

In another area of the same sheet I combine information of all like numbers
from the top row using sum if. The result would be:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

I would like to use hlookup, but it will only find the first number and does
not show if there is a second.

I hope you can understand my ramblings.


"T. Valko" wrote:

You would need a more complicated formula. How would you want the results
displayed:

98765...result1...result2...result3

Or

98765...result1
..............result2
..............result3

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Thanks for the help.

What would you do if there were three or more possibilities? Would you
just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup for the
1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have
the
ability to choose either X or R in Column B. Some times Column A has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show up.
If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

Not sure I follow you. Do you mean instead of 0 you want a blank cell?

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
I was trying to cover a few different scenarios with one question. Your
original answer took care of most. For the complicated one I will use
hlookup. Here is an example of the complicated one:

74019 74019 39008 39008 69861 74831 74831 74831
row of cells with data for other uses
row of cells with data for other uses
3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4"
empty cell
1,000 5,000 600 800 3,000 50 300
0

In another area of the same sheet I combine information of all like
numbers
from the top row using sum if. The result would be:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

I would like to use hlookup, but it will only find the first number and
does
not show if there is a second.

I hope you can understand my ramblings.


"T. Valko" wrote:

You would need a more complicated formula. How would you want the results
displayed:

98765...result1...result2...result3

Or

98765...result1
..............result2
..............result3

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Thanks for the help.

What would you do if there were three or more possibilities? Would you
just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup for the
1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have
the
ability to choose either X or R in Column B. Some times Column A
has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show
up.
If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

A zero would be best.

Don

"T. Valko" wrote:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

Not sure I follow you. Do you mean instead of 0 you want a blank cell?

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
I was trying to cover a few different scenarios with one question. Your
original answer took care of most. For the complicated one I will use
hlookup. Here is an example of the complicated one:

74019 74019 39008 39008 69861 74831 74831 74831
row of cells with data for other uses
row of cells with data for other uses
3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4"
empty cell
1,000 5,000 600 800 3,000 50 300
0

In another area of the same sheet I combine information of all like
numbers
from the top row using sum if. The result would be:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

I would like to use hlookup, but it will only find the first number and
does
not show if there is a second.

I hope you can understand my ramblings.


"T. Valko" wrote:

You would need a more complicated formula. How would you want the results
displayed:

98765...result1...result2...result3

Or

98765...result1
..............result2
..............result3

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Thanks for the help.

What would you do if there were three or more possibilities? Would you
just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup for the
1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I have
the
ability to choose either X or R in Column B. Some times Column A
has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show
up.
If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

Try this:

This data in the range A1:H1
74019,74019,39008,39008,69861,74831,74831,74831

This data in the range A4:H4
3/4",3/4",1/2",3/4",1/2",1/2",3/4"

This data in the range A5:H5
1000,5000,600,800,3000,50,300,0

These headers in the range B10:E10
74019,39008,69861,74831

A11 = 3/4"
A12 = 1/2"

Enter this formula in B11:

=SUMPRODUCT(--($A$1:$H$1=B$10),--($A$4:$H$4=$A11),$A$5:$H$5)

Copy across to E11 then down to B12:E12.

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
A zero would be best.

Don

"T. Valko" wrote:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

Not sure I follow you. Do you mean instead of 0 you want a blank cell?

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
I was trying to cover a few different scenarios with one question. Your
original answer took care of most. For the complicated one I will use
hlookup. Here is an example of the complicated one:

74019 74019 39008 39008 69861 74831 74831
74831
row of cells with data for other uses
row of cells with data for other uses
3/4" 3/4" 1/2" 3/4" 1/2" 1/2"
3/4"
empty cell
1,000 5,000 600 800 3,000 50
300
0

In another area of the same sheet I combine information of all like
numbers
from the top row using sum if. The result would be:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

I would like to use hlookup, but it will only find the first number and
does
not show if there is a second.

I hope you can understand my ramblings.


"T. Valko" wrote:

You would need a more complicated formula. How would you want the
results
displayed:

98765...result1...result2...result3

Or

98765...result1
..............result2
..............result3

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Thanks for the help.

What would you do if there were three or more possibilities? Would
you
just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup for
the
1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I
have
the
ability to choose either X or R in Column B. Some times Column A
has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show
up.
If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

I'm not sure if you are still monitoring this thread. If you are, thank you
very much. Your formula works as needed.



"T. Valko" wrote:

Try this:

This data in the range A1:H1
74019,74019,39008,39008,69861,74831,74831,74831

This data in the range A4:H4
3/4",3/4",1/2",3/4",1/2",1/2",3/4"

This data in the range A5:H5
1000,5000,600,800,3000,50,300,0

These headers in the range B10:E10
74019,39008,69861,74831

A11 = 3/4"
A12 = 1/2"

Enter this formula in B11:

=SUMPRODUCT(--($A$1:$H$1=B$10),--($A$4:$H$4=$A11),$A$5:$H$5)

Copy across to E11 then down to B12:E12.

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
A zero would be best.

Don

"T. Valko" wrote:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

Not sure I follow you. Do you mean instead of 0 you want a blank cell?

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
I was trying to cover a few different scenarios with one question. Your
original answer took care of most. For the complicated one I will use
hlookup. Here is an example of the complicated one:

74019 74019 39008 39008 69861 74831 74831
74831
row of cells with data for other uses
row of cells with data for other uses
3/4" 3/4" 1/2" 3/4" 1/2" 1/2"
3/4"
empty cell
1,000 5,000 600 800 3,000 50
300
0

In another area of the same sheet I combine information of all like
numbers
from the top row using sum if. The result would be:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

I would like to use hlookup, but it will only find the first number and
does
not show if there is a second.

I hope you can understand my ramblings.


"T. Valko" wrote:

You would need a more complicated formula. How would you want the
results
displayed:

98765...result1...result2...result3

Or

98765...result1
..............result2
..............result3

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Thanks for the help.

What would you do if there were three or more possibilities? Would
you
just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup for
the
1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list I
have
the
ability to choose either X or R in Column B. Some times Column A
has
multiple combinations of information.

In another area of the sheet I need to have both the X and R show
up.
If
I
use vlookup the first instance only, is shown.

What can I do?

Two possible values with a lookup?
 

I'm not sure if you are still monitoring this thread.

I watch threads I've replied to for about 10 days.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
I'm not sure if you are still monitoring this thread. If you are, thank
you
very much. Your formula works as needed.



"T. Valko" wrote:

Try this:

This data in the range A1:H1
74019,74019,39008,39008,69861,74831,74831,74831

This data in the range A4:H4
3/4",3/4",1/2",3/4",1/2",1/2",3/4"

This data in the range A5:H5
1000,5000,600,800,3000,50,300,0

These headers in the range B10:E10
74019,39008,69861,74831

A11 = 3/4"
A12 = 1/2"

Enter this formula in B11:

=SUMPRODUCT(--($A$1:$H$1=B$10),--($A$4:$H$4=$A11),$A$5:$H$5)

Copy across to E11 then down to B12:E12.

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
A zero would be best.

Don

"T. Valko" wrote:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

Not sure I follow you. Do you mean instead of 0 you want a blank cell?

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
I was trying to cover a few different scenarios with one question.
Your
original answer took care of most. For the complicated one I will
use
hlookup. Here is an example of the complicated one:

74019 74019 39008 39008 69861 74831 74831
74831
row of cells with data for other uses
row of cells with data for other uses
3/4" 3/4" 1/2" 3/4" 1/2" 1/2"
3/4"
empty cell
1,000 5,000 600 800 3,000 50
300
0

In another area of the same sheet I combine information of all like
numbers
from the top row using sum if. The result would be:

74019 39008 69861 74831
6,000 800 0 300 this line for 3/4"
0 600 3,000 50 this line for 1/2"

If there is no 3/4" or 1/2", I do not want anything done.

I would like to use hlookup, but it will only find the first number
and
does
not show if there is a second.

I hope you can understand my ramblings.


"T. Valko" wrote:

You would need a more complicated formula. How would you want the
results
displayed:

98765...result1...result2...result3

Or

98765...result1
..............result2
..............result3

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Thanks for the help.

What would you do if there were three or more possibilities?
Would
you
just
string if statements along or is there a better way?



"T. Valko" wrote:

If there are *only the 2 possibilities* you can use the lookup
for
the
1st
instance and then something like this for the 2nd:

X1 formula:

=VLOOKUP(98765,A2:D5,2,0)

Y1 formula:

=IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"")

--
Biff
Microsoft Excel MVP


"Homer" wrote in message
...
Here is what I have:

A B C D
12345 X 9 a
98765 R 3 t
98765 X 4 b
56489 X 7 p

Column B has two options X or R. Through validation and list
I
have
the
ability to choose either X or R in Column B. Some times
Column A
has
multiple combinations of information.

In another area of the sheet I need to have both the X and R
show
up.
If
I
use vlookup the first instance only, is shown.

What can I do?