Match 2 Texts & Show a 3rd

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
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
 
steven.holloway said:
Thanks Max, this works, ..
Click to expand...

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.
Click to expand...

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?
Click to expand...

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

---
 
Back
Top