Sorry about the last post , here is an other copy

in sheet R! A D F
1 num time price
2 1 10:25 st 90
3 2 11:00 st 190
4 3 11:35 st 80
5 4 12:00 st 110
6 5 1:00 st 110
7 6 1:20 st 90
8 7 2:15 st 70
9 8 2:55 st 90
10 9 3:40 st 110
11 10 4:20 st 110
12 11 6:00 st 80

in sheet H! A
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 4
12 4

in sheet RC! H
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 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

--
bill gras

What happened to your previous post?..

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
---------------
Jacob Skaria


"bill gras" wrote:

Sorry about the last post , here is an other copy

in sheet R! A D F
1 num time price
2 1 10:25 st 90
3 2 11:00 st 190
4 3 11:35 st 80
5 4 12:00 st 110
6 5 1:00 st 110
7 6 1:20 st 90
8 7 2:15 st 70
9 8 2:55 st 90
10 9 3:40 st 110
11 10 4:20 st 110
12 11 6:00 st 80

in sheet H! A
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 4
12 4

in sheet RC! H
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 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

--
bill gras

Paste this formula in Sheet RC J2 Cell

=IF(H!A2=H2,IF(ISNA(TEXT(VLOOKUP(H2,'R'!A:D,4,FALS E),"HH:MM")&"
"&VLOOKUP(H2,'R'!A:F,6,FALSE)),"Lookup Value is not present in
SheetR",TEXT(VLOOKUP(H2,'R'!A:D,4,FALSE),"HH:MM")& "
"&VLOOKUP(H2,'R'!A:F,6,FALSE)),"")

Copy the J2 cell formula and paste it for the remaining cells.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"bill gras" wrote:

Sorry about the last post , here is an other copy

in sheet R! A D F
1 num time price
2 1 10:25 st 90
3 2 11:00 st 190
4 3 11:35 st 80
5 4 12:00 st 110
6 5 1:00 st 110
7 6 1:20 st 90
8 7 2:15 st 70
9 8 2:55 st 90
10 9 3:40 st 110
11 10 4:20 st 110
12 11 6:00 st 80

in sheet H! A
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 4
12 4

in sheet RC! H
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 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

--
bill gras

Hi Ms-Exl-Learner
Your formula returned a "False" I checked and re-checked
Thanks so far
bill gras


"Ms-Exl-Learner" wrote:

Paste this formula in Sheet RC J2 Cell

=IF(H!A2=H2,IF(ISNA(TEXT(VLOOKUP(H2,'R'!A:D,4,FALS E),"HH:MM")&"
"&VLOOKUP(H2,'R'!A:F,6,FALSE)),"Lookup Value is not present in
SheetR",TEXT(VLOOKUP(H2,'R'!A:D,4,FALSE),"HH:MM")& "
"&VLOOKUP(H2,'R'!A:F,6,FALSE)),"")

Copy the J2 cell formula and paste it for the remaining cells.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"bill gras" wrote:

Sorry about the last post , here is an other copy

in sheet R! A D F
1 num time price
2 1 10:25 st 90
3 2 11:00 st 190
4 3 11:35 st 80
5 4 12:00 st 110
6 5 1:00 st 110
7 6 1:20 st 90
8 7 2:15 st 70
9 8 2:55 st 90
10 9 3:40 st 110
11 10 4:20 st 110
12 11 6:00 st 80

in sheet H! A
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 4
12 4

in sheet RC! H
1 num
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 4
11 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

--
bill gras