Lookup in more groups

[jkrons]

I have some data like this

A B C
IN SA Formula
IN SB Formula
IN SC Formula
IN SD Formula

F SA Customer 1
F SB
F SC
F SD

J SA
J SB Customer 2
J SC
J SD

T SA
T SB
T SC
T SD Customer 3

________________________________________________

Now what I like is a formula in the C-column where I have typed
Formula. The formula should return the value in the C-column for each
inital like:

IN SA Customer 1
IN SB Customer 2
IN SC
IN SD Customer 3

so I can get an overview over which technicians is already booked for
a customer. F, J, T are projects, and in the overview Ii do not care,
which projects the technicians are booked for. I just like to know if
the are booked or not and for which customer.

Any suggestions?

Jan

 

[Per Jessen]

Hi

With your data in B7:C20, use this formula:

=VLOOKUP(B2,$B$7:$C$20,2)

Regards,
Per

 

[Jan Kronsell]

Hi Per

I tried that approach alreadty but it does not work as all the lookup value
(initals) are repeated several times in the B-column, and the return value
does not necessarily appear on the first occurrence. My own MULVLOOKUP UDF
that accepts non unique lookup values does'nt do the trick either, because
it "expects me to tell", from which occurence, I like the return value.

Basically what I need, is a formula, that looks at all occurences of an
initial (specified in B1:B4) in the B-coloum, then looks at the value in the
C-column for that initial, and if theC-column is not empty, returns the
value from the c-column. If the c-column is empty, then the formula must
look for the next occurence of the same lookup value, look in the c-colum,
and so on. If no C-column value is found for a initial, the return value
should be "".

Jan

 

[T. Valko]

Try this array formula** :

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(C$7:C$20,MATCH(1,(B$7:B$20=B2)*(C$7:C$20<>""),0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down as needed.

 

[Jan Kronsell]

Thank you. just what i was looking for.

Jan

Try this array formula** :

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(C$7:C$20,MATCH(1,(B$7:B$20=B2)*(C$7:C$20<>""),0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the SHIFT key then hit ENTER.

Copy down as needed.

 

[Jan Kronsell]

A single qquestion though. Why the "zzzzz"?

Jan

Thank you. just what i was looking for.

Jan

 

[T. Valko]

A single qquestion though. Why the "zzzzz"?

The INDEX/MATCH portion of the formula will return either a customer name or
a #N/A error. The customer name is obviously a text string.

The CHOOSE function generates a 2 element lookup_vector For the LOOKUP
function. Those elements are a blank "" and the result of the INDEX/MATCH.

The way that LOOKUP works is if every value in the lookup_vector is less
than the lookup_value, the result will be the *last* element in the
lookup_vector that is less than the lookup_value.

If the result of INDEX/MATCH is Customer1 then the lookup_vector contains:

{"",Customer1}

In Excel text has degrees of value just like numbers. Just like 2 is greater
than 1, so is B greater than A. We use this logic with the LOOKUP function
to make sure we get the correct result.

The lookup_value zzzzz probably (almost certainly!) has a higher value than
any of your customer names. For example: zzzzz has a much higher value than
Customer1.

Since zzzzz is greater than any element of the lookup_vector {"",Customer1}
the result is the *last* element of the lookup_vector that is less than the
lookup_value zzzzz. The last element of the lookup_vector that is less than
zzzzz is Customer1.

When the INDEX/MATCH returns the error #N/A then the lookup_vector is:
{"",#N/A}. LOOKUP ignores errors so in this case the *last* element of the
lookup_vector that is less than the lookup_value zzzzz is "". So the result
in this case is a blank.



exp101

 

[Jan Kronsell]

Thank you for an excellent explanation.

Jan

The INDEX/MATCH portion of the formula will return either a customer
name or a #N/A error. The customer name is obviously a text string.

The CHOOSE function generates a 2 element lookup_vector For the LOOKUP
function. Those elements are a blank "" and the result of the
INDEX/MATCH.
The way that LOOKUP works is if every value in the lookup_vector is
less than the lookup_value, the result will be the *last* element in
the lookup_vector that is less than the lookup_value.

If the result of INDEX/MATCH is Customer1 then the lookup_vector
contains:
{"",Customer1}

In Excel text has degrees of value just like numbers. Just like 2 is
greater than 1, so is B greater than A. We use this logic with the
LOOKUP function to make sure we get the correct result.

The lookup_value zzzzz probably (almost certainly!) has a higher
value than any of your customer names. For example: zzzzz has a much
higher value than Customer1.

Since zzzzz is greater than any element of the lookup_vector
{"",Customer1} the result is the *last* element of the lookup_vector
that is less than the lookup_value zzzzz. The last element of the
lookup_vector that is less than zzzzz is Customer1.

When the INDEX/MATCH returns the error #N/A then the lookup_vector is:
{"",#N/A}. LOOKUP ignores errors so in this case the *last* element
of the lookup_vector that is less than the lookup_value zzzzz is "".
So the result in this case is a blank.



exp101

 

[T. Valko]

You're welcome. Thanks for the feedback!