Double vlookup - lookup into 2 different column

[spari77]

Hi
I have a raw data as below:-
ID Num STATION RESULTS DETAIL1
A 1 PASS
A 2 PASS
A 3 FAIL NO CONNECTION
B 1 PASS
C 1 FAIL NO POWER
C 2 PASS
D 1 PASS
D 3 FAIL SOUND
F 1 PASS
F 3 PASS
B 2 PASS
D 2 FAIL
F 2 PASS
B 3 PASS

How do i do a vlookup table (in a separate sheet) to return me th
value of ColumnC (Results) for the below
ID Num Station1 STATION2 STATION3
A PASS PASS FAIL
B ...... ....... .......

Please help Neeed this fast

 

[Max]

Assume your data below is in Sheet1, cols A to D
data from row2 down

ID Num STATION RESULTS DETAIL1
A 1 PASS
A 2 PASS
A 3 FAIL NO CONNECTION
B 1 PASS

(Note: "ID Num" is presumed in col A,
"Station" in col B, "Results" in col C)

In Sheet1, using an empty col, say col E

Put in E2: =TRIM(A2&$B$1&B2)
Copy E2 down as many rows as there is data in cols A to C

In a new Sheet2
-------------------
Assume the labels are:

In A1: ID Num
In B11 are: Station1, Station2, Station3

Listed down in A2:A7 are: A,B,C,D,E,F

Put in B2:

=IF(ISNA(MATCH($A2&B$1,Sheet1!$E:$E,0)),"--",
OFFSET(Sheet1!$A$1,MATCH($A2&B$1,Sheet1!$E:$E,0)-1,2))

Copy B2 across to D2, then copy down to D7

In B27 will be returned the Results from Sheet1
for the corresponding ID Num and Station
(Unmatched cases, if any, will return "--")

 

[spari77]

Thanks. That works great

 

[Max]

You're welcome !
Glad it worked for you