Formula for identifying results in an array

I am currently using vlookup to do this but I am sure there is a much, much better way.
I have SHEET 1 as a sheet for marking a relevant "X" on the correct factor for each area and in Sheet 2 the results for the different areas.
What formula should I be using for the bold results in SHEET 2?

SHEET 1
_____________B1: Area1___C1: Area2___D1: Area3
A2: Factor1___B2: X_______C2: X
A3: Factor2
A4: Factor3__________________________D4: X

SHEET 2
Area1 Factor1
Area2 Factor1
Area3 Factor3

Many thanks.

Hi purplelily,

Try using below formula:-

Formula:

Please Login or Register  to view this content.

enter this with combination: ctrl shift enter

see attachment:- formula for indentifying array.xlsx

Regards,
DILIPandey
<click on below * if this helps>

Your question can be read many ways.
Can you clarify what are you really trying to do?

Is the data/result in Sheet2 in one or two columns?

Here's another take.

Originally Posted by dilipandey

Hi purplelily,

Try using below formula:-

Formula:

Please Login or Register  to view this content.

enter this with combination: ctrl shift enter

see attachment:- Attachment 210379

Regards,
DILIPandey
<click on below * if this helps>

Thank you so much for your help - exactly what I was looking for!

However, if the range in Sheet 1 is empty, i.e. without any Xs, the result in Sheet 2 will show Factor 1 for all the areas.
How do I get around this?

Hi purplelily,

However, if the range in Sheet 1 is empty, i.e. without any Xs, the result in Sheet 2 will show Factor 1 for all the areas.

You can use a If logic to handle that, see attached:-

formula for indentifying array(1).xlsx

Regards,
DILIPandey
<click on below * if this helps>

You are welcome purplelily

Cheers


Regards,
DILIPandey
<click on below * if this helps>

Just for fun

This method, using names, returns multiple instances of "x" for any "area"

Put an "x" in any cell in Sheet1 B2:D3 to see the result in Sheet2. ("X" 1/2/3 etc is the instance number.)

The table in Sheet1 is fully dynamic, add as many rows/columns as you might need.
Then make the relevant query in sheet2, drag B2 across and Down as required.