Double vlookup - lookup into 2 different column

  • Thread starter Thread starter spari77
  • Start date Start date
S

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

(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 B1:D1 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 B2:D7 will be returned the Results from Sheet1
for the corresponding ID Num and Station
(Unmatched cases, if any, will return "--")
 
Back
Top