Lookup and return multi data

Don't see an attachment.

Let's assume you have a list of costs in column A (headed 'Cost'), range A2:A10, and in column B you have a list of fruit, range B2:B10.

. . A . . B
.1 Cost .Fruit
.2 £1.00 Apples
.3 £1.10 Oranges
.4 £1.00 Bananas
.5 £1.00 Pears
.6 £1.10 Pineapples
.7 £1.20 Peaches
.8 £1.00 Mandarins
.9 £1.10 Kiwis
10 £1.20 Mangoes

You want to list the fruit which costs £1.00, which is in cell D2, with the results in column E2 downwards.

Put this in E2:

Formula:

Please Login or Register  to view this content.

It's an array formula, so enter it using Ctrl-Shift-Enter not just Enter. You'll see curly brackets {} appear round the formula (don't enter them yourself).
Drag the formula down as far as you need.

You should get the answers:

Apples
Bananas
Pears
Mandarins
#NUM!
#NUM!

To get rid of the #NUM! errors, put the formula in E2 inside an error checker:

Formula:

Please Login or Register  to view this content.

Again, enter with Ctrl-Shift-Enter and drag down.

Obviously, change the ranges to match what your ranges are.

Does that do what you need?

ps to attach a file, click 'Go Advanced' then scroll down and click 'Manage Attachments'

If you want multiple results to appear in a single cell that will require a VBA function.

Putting multiple results in separate cells would be easier and can be done with formulas.