match formula needed

[bill gras]

in sheet R! A D F in sheet H! A in sheet RC!
H
1 num time price 1 num
1 num
2 1 10:25 st 90 2 1
2 1
3 2 11:00 st 190 3 1
3 1
4 3 11:35 st 80 4 1
4 1
5 4 12:00 st 110 5 2
5 2
6 5 1:00 st 110 6 2
6 2
7 6 1:20 st 90 7 3
7 3
8 7 2:15 st 70 8 3
8 3
9 8 2:55 st 90 9 3
9 3
10 9 3:40 st 110 10 4
10 4
11 10 4:20 st 110 11 4
11 4
12 11 6:00 st 80 12 4
12 4
all columns down to 2500 rows, all numbers are at random lots

I have a formula in sheet RC! as follows:-
=IF(H!A2=H2,CONCATENATE(MID(R!D2,1,5)," ",R!F2))
This formula gives me in sheet RC! cell J2 10:25 90
J3 11:00 190
J4 11:35 80
J5 12:00 110
J6 1:00 110
J7 1:20 90
and so on

I need to have in cell J2 10:25 90
J3 10:25 90
J4 10:25 90
J5 11:00 190
J6 11:00 190
J7 11:35 80
and so on
Is there any one that can help me please ?

much appreciated

 

[JLatham]

The editor here has screwed up your sample data to the point that I don't
think many will be able to help you with it.

You might separate the 3 sheet's data into 3 groups, showing first what is
in the columns in sheet R, then after that, the data in sheet H and finally
the data in sheet RC.

 

[Jacob Skaria]

Hi Bill

Few points

--Refer help on VLOOKUP()
--Instead of CONCATENATE() you can use the ampersand &
--You can use the LEFT() function if you are looking to extract text from
the beginning of another text

Try the below in Sheet RC cell J2 and copy down as required. If sheet RC H2
match with Sheet H cell A2 then lookup the value in cell H2 and combine the
values from Sheet RC ColD and ColF. Else..If sheet RC H2 do not match with
Sheet H cell A2 then return blank.

=IF(H!A2=H2,LEFT(VLOOKUP(H2,R!A:F,4,0),5) & " " & VLOOKUP(H2,R!A:F,6,0),"")

If you dont want the first condition to be checked then the below will do
which will straight away do the lookup and concatenate .

=LEFT(VLOOKUP(H2,R!A:F,4,0),5) & " " & VLOOKUP(H2,R!A:F,6,0)

If this post helps click Yes