Two possible values with a lookup?

[Homer]

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?

 

[T. Valko]

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,A25,2,0)

Y1 formula:

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

 

[Shane Devenshire]

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&":B4"),2,0)

 

[Homer]

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]

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

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

Or

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

 

[Homer]

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]

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?

 

[Homer]

A zero would be best.

Don

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

 

[T. Valko]

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.

 

[Homer]

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]

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!