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?

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?

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?

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?

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?

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?

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?