Index Match Problem
 

In column A1 is my pay rate.
In column A2 is my car count.
In column A3 is my type service.
I want A1, A2 and A3 to all be recongized for the answer.
Example:
In A1 I have the letter B
In A2 I have the number 50
In A3 I have the letter L
With these 3 contents in the cells, I need a answer of 100.
NOW;
If I change A1 to the letter C
If I leave A2 at 50
If I change A3 to F
I would like the answer to be 200
I can do the regular Index function ok. I understand the tables.
It's this third item in column A3 I am having a problem with(I think).
I hope this is enough information without getting real deep in the
formula.
Thanks in advance for any help.

One take on your post ..

Assuming you have a reference table
in Sheet1, cols A to D, data from row2 down
-------------
PayRate CarCount TypeSvc Value
C 50 F 200
D 100 P 300
B 50 L 100

In Sheet2
-------------
If you have the values of:
PayRate, CarCount, TypeSvc
listed down in cols A to C, from row2 down,
viz.:
In A2:C2 : B, 50, L
In A3:C3 : C, 50, F

Put in D2:
=INDEX(Sheet1!$D$2:$D$100,MATCH(1,(Sheet1!$A$2:$A$ 100=A2)*(Sheet1!$B$2:$B$10
0=B2)*(Sheet1!$C$2:$C$100=C2),0))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy D2 down

Col D will retrieve the values from col D in Sheet1
corresponding to inputs in cols A to C, i.e.
for the sample inputs above, you'll get:

B 50 L 100
C 50 F 200

Or perhaps better with an error-trap,
put instead in D2 and array-enter:

=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$100=A2)*(Sheet1!$ B$2:$B$100=B2)*(Sheet1!$C$
2:$C$100=C2),0)),"",INDEX(Sheet1!$D$2:$D$100,MATCH (1,(Sheet1!$A$2:$A$100=A2)
*(Sheet1!$B$2:$B$100=B2)*(Sheet1!$C$2:$C$100=C2),0 )))

Copy D2 down, as before

The above will now return blanks: "" (instead of #NAs)
for any unmatched "combination" inputs in cols A to C

Adapt the ranges to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Scooterdog" wrote in message
...
In column A1 is my pay rate.
In column A2 is my car count.
In column A3 is my type service.
I want A1, A2 and A3 to all be recongized for the answer.
Example:
In A1 I have the letter B
In A2 I have the number 50
In A3 I have the letter L
With these 3 contents in the cells, I need a answer of 100.
NOW;
If I change A1 to the letter C
If I leave A2 at 50
If I change A3 to F
I would like the answer to be 200
I can do the regular Index function ok. I understand the tables.
It's this third item in column A3 I am having a problem with(I think).
I hope this is enough information without getting real deep in the
formula.
Thanks in advance for any help.