IF Function with INDEX and MATCH Function

[palmese]

I have the following formula, which is getting me very close to what I need:

=IF(B37="Fairchild Imaging","TCGROUP&quot&(INDEX($B$50:$C$681,MATCH($A$66,$B$50:$B$681,0),2))

Some Background:

I have the user make a selection from a ComboBox (i.e. user chooses "Option 1" and cell B37 displays "VENLLC" based on the INDEX/MATCH function). Next, based on the selection from ComboBox1, ComboBoxes 2-11 are populated with choices for the user to select. Out of the twenty or so choices in CB's 2-11, cell B37 should display "VENLLC" upon the selection of any choice in the CB -- with the exception of the user selecting "Fairchild Imaging".

If the user chooses "Fairchild Imaging", I need cell B37 to say "TCGROUP". When I use the above function with the "&" sign, I am getting "TCGROUPVENLLC" and I need just "TCGROUP". My guess is that the solution lies in the "&" property. I essentially want to keep my INDEX/MATCH function the same but need a way to incorporate the "Fairchild Imaging" exception.

Any suggestions? Thanks.

 

[combo]

Will

=IF(B37="Fairchild Imaging","TCGROUP",INDEX($B$50:$C$681,MATCH($A$66,$B$50:$B$681,0),2))

display what you want to?

 

[palmese]

Yep, that did it. Many thanks!!!

 

[xlbo]

Hey Palmese - if Combo's answer helped you (which I'm pretty sure it did), you should award a star. This is done by clicking on the "Mark this post as a helpful or expert post" under the post that helped you. This is the Tek-Tips recognised way of saying thanks and it also helps people searching the archives to find good answers to questions that are similar to theirs, without having to re-post. Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?