Match 2 Texts & Show a 3rd

[Guest]

HELP...... Please!

Data Set Example (Sheet2):
A B C D E
1 EmpID Dept Site Start End
2 10 A Yellow May'07
3 500 B Green May'07 Jun'07
4 26 C Brown May'07
5 500 A Pink Jun'07

Step 1:
I have another source (Sheet1) that provides the EmpID (A1 = 500) & the Dept
(B1 = A) and I need a formula that will give me the Site from the above in C1
on sheet1(Should = Pink).

Step 2:
However as you can see, the EmpID table above can have duplications and so I
need the formula to look at both columns (in the row) at the same time and
not match any entry in one column with any entry in the other column.

NB:
The date columns may need to be brought into play, but I would prefer the
formula not to need to use these.

Many thanks for any help possible.
Steve

 

[Guest]

Steven,

I way

=INDEX(C1:C10,MATCH(D1&E1,A1:A10&B1:B10,0))

This looks for the 2 values held in D1 & E1 in cols A & B and returns the
corresponding value in C.

It's an array so enter with Ctrl+Shift+enter

Mike

 

[Guest]

One way ..

In Sheet1,

Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER:
=INDEX(Sheet2!C$2:C$5,MATCH(1,(Sheet2!A$2:A$5=A1)*(Sheet2!B$2:B$5=B1),0))
Copy down as desired to return correspondingly for other pairs of inputs in
cols A and B. Adapt the ranges to suit.

 

[Guest]

Thanks Max, this works, but I have a 2 further questions;

1. Are there any issues with using these "array-entered" formulas? - For
example, I have used this type of formula in the past and once I copied to a
large area, the performance of the spreadsheet fade dramatically - and made
the file pretty much unusable.

2. Can you add non "array-entered" formulas to an "array-entered" formula? -
For example if I want to adapt the below formula to start with an
"if(iserror(" statement?

Many thanks again
Steve

 

[Max]

Thanks Max, this works, ..

No prob. Glad it worked.

but I have a 2 further questions;
1. Are there any issues with using these "array-entered" formulas? - For
example, I have used this type of formula in the past and once I copied to
a
large area, the performance of the spreadsheet fade dramatically - and
made
the file pretty much unusable.

Yes, that's a trade-off. I'd manage it by setting calc to manual mode, go
about updating here and there w/o any fuss, then pressing F9 to calc only
when I'm done with the updates, or whenever required.

And as a non-array alternative, you could also use a helper col to concat
the 2 cols A and B in Sheet2, then just do a normal INDEX/MATCH on similarly
concatenated lookup values in Sheet1.

2. Can you add non "array-entered" formulas to an "array-entered"
formula? -
For example if I want to adapt the below formula to start with an
"if(iserror(" statement?

Yes, of course. But you would need to array-enter the combined formula.

---

 

[Guest]

Many thanks again Max (Mike H your help was appreciated too)

Kind regards
Steve

 

[Max]

welcome, Steve.