Two possible values with a lookup?

  • Thread starter Thread starter Homer
  • Start date Start date
H

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?
 
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"),"")
 
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)
 
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?
 
You would need a more complicated formula. How would you want the results
displayed:

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

Or

98765...result1
..............result2
..............result3
 
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.
 
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.
Click to expand...

Not sure I follow you. Do you mean instead of 0 you want a blank cell?
 
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.
 
I'm not sure if you are still monitoring this thread. If you are, thank you
very much. Your formula works as needed.
 
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!
 
Back
Top