Lookup

  • Thread starter Thread starter mathewheys
  • Start date Start date
M

mathewheys

Hi All

I have a table that contains the following infromation in A2:FT6010
I need to lookup the information in FT witch is column 175 that matches
critiera from A (date) and Name

If 1/2/05 and Kerry = 94.96%
EG
A[INDENT ]B[INDENT ]FT
1/02/2005
Mathew
100.00%
1/02/2005
Kelly[INDENT ]98.25%
1/02/2005
Paul[INDENT ]92.12%
1/02/2005
Kerry[INDENT ]94.96%
1/02/2005[INDENT ]Rick[INDENT ]98.18%
2/02/2005
Mathew[INDENT ]99.85%
2/02/2005[/NDENT ]Kelly[INDENT ]75.12%

Thanks​
 
One way ..

Assuming the source data is in Sheet1, with dates in col A, names in col B,
percentages in col FT, data from row2 down

In Sheet2

With the dates in col A, names in col B, form row1 down,
put in the formula bar for C1 and array-enter
(i.e. press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$100=A1)*(Sheet1!$B$2:$B$100=B1),0)),"",INDE
X(Sheet1!$FT$2:$FT$100,MATCH(1,(Sheet1!$A$2:$A$100=A1)*(Sheet1!$B$2:$B$100=B
1),0)))

Format C1 as percentage, fill down until the last row of data in cols A and
B

If you have in A1: 2/1/05, in B1: Kelly, then C1 will return: 98.25%
Unmatched cases, if any, will return blanks: ""

Adapt the ranges to suit ..
 
Hi!

Try this:

Enter the criteria in a couple of cells:

A1 = 1/02/2005
B1 = Kerry

=SUMPRODUCT(--(A2:6010=A1),--(B2:B6010=B1),FT2:FT6010)

Biff
 
Back
Top