Looking up multiple text items

[Jaap's Barber]

Sorry I'm not overly hot with excel so please excuse me if my terminology is slighty wayward!

I have 2 spreadsheets, one I've set up myself the other is a dump out from another package so I have no control of how it is presented etc

I've created a simplified version of my problem on the attached. Sheet 1 is mine, sheet 2 is the automated dump.

I need in e5 of my sheet to search sheet 2 column c for text that contains '2007' and 'red dog' and column a for text that = A1, once this has been identified i need it to display the cell in column b of the row that matches the text. Any suggestions on what formula I need?

The icing on the cake would be if I could then have the row in sheet 2 automatically highlighted to show it has being done,

Thanks in advance.

JB

[teylyn]

Hello Jaap's Barber,

welcome to the forum.

One way of achieving your goal is to break up column C in Sheet2 with Text to colums. If your original data has content in the adjacent column D, insert a new column, then highlight the values in column C and click Data - Text to columns - fixed width -- and set the line between the two columns to start just before the text afte the year number, like

2008 |red dog

this will separate the year number from the rest of the text in column C.

following that, you can then use a helper column to construct your lookup value in column E of Sheet2

=D1&" "&A1&" "&C1

With this in place, you can use this formula in E5 of Sheet1:

=IF(ISNA(MATCH($C5&" "&$D5&" "&E$4,Sheet2!$E$1:$E$7,0)),"",INDEX(Sheet2!$B$1:$B$7,MATCH($C5&" "&$D5&" "&E$4,Sheet2!$E$1:$E$7,0)))

format the result with the custom format

"£"#,##0;[Red]-"£"#,##0

To highlight the matches, use Conditional Formatting with Formula Is

=ISNUMBER(E5)

see attachment for a working example.

hth

[zbor]

or this maybe?

Edit: and of course B column....

[DonkeyOte]

Not saying I would recommend this but based on your sample file you could use the below without needing to adapt anything:

Sheet1!E5:
=SUMPRODUCT(--(Sheet2!$A$1:$A$7=$D5),--(Sheet2!$C$1:$C$7=E$4&" "&LOOKUP(REPT("Z",255),$C$5:$C5)),Sheet2!$B$1:$B$7)
applied to matrix E5:G13

EDIT:
of course if you concatenate Sheet2 Col A with Sheet2 Col C - eg: Sheet2!D1: =A1&" "&C1 ... you could use a SUMIF which would be pretty efficient

[teylyn]

DO, are you saying you are recommending something you don't really recommend?? Will that then make the formula invisible?

[DonkeyOte]

I don't think I recommended anything... I think I said could...

That being said using a basic concatenation of A & C and SUMIF would not be an expensive approach...
However... I suspect, based on OP, we're only touching the tip of the iceberg... if the data is not stored local to the matrix output then SUMIF is potentially precluded - all viable function based approaches at that point would be inefficient.

[Jaap's Barber]

Many thanks for the responses. Fingers crossed I can get it right in work tomorrow.....

Cheers

JB