ExcelBanter - Match with copy

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Match with copy (https://www.excelbanter.com/excel-discussion-misc-queries/248359-match-copy.html)

I have 2 sheets. I need to compare F92 of Sheet-1 with Sheet-2 range from
F21:F9500. If there is a match then I need to see corresponding value in
cell I of sheet-2. If that value is R, then on Sheet-1, next to F92 I
need to insert value of cell J from Sheet-2 into G92 of Sheet-1. I need to
check Sheet-1 F92 thru F2000. As a result, I will see some cells with values
in column J of Sheet-1. How can I do this?

Sheet-1:
Col-F Col-G
1234567890
1234576889
1234864534
1234895498
1234922374
1234944784

Sheet-2:
Col-F Col-I Col-J
1234567890 C
1234573245
1234576889 R FedEx 293833849505
1234895498
1234921132 X
1234922374 R UPS 1Z585960604968

Result Should be:
Sheet-1:
Col-F Col-G
1234567890
1234576889 FedEx 293833849505
1234864534
1234895498
1234922374 UPS 1Z585960604968
1234944784

Vic,

Try this

=IF(COUNTIF('Sheet-2'!$F$21:$F$9500,F92)=0,"",IF(VLOOKUP(F92,'Sheet-2'!$F$21:$J$9500,4,FALSE)="r",VLOOKUP(F92,'Sheet-2'!$F$21:$J$9500,5,FALSE),""))

all on 1 line

Mike

"Vic" wrote:

I have 2 sheets. I need to compare F92 of Sheet-1 with Sheet-2 range from
F21:F9500. If there is a match then I need to see corresponding value in
cell I of sheet-2. If that value is R, then on Sheet-1, next to F92 I
need to insert value of cell J from Sheet-2 into G92 of Sheet-1. I need to
check Sheet-1 F92 thru F2000. As a result, I will see some cells with values
in column J of Sheet-1. How can I do this?

Sheet-1:
Col-F Col-G
1234567890
1234576889
1234864534
1234895498
1234922374
1234944784

Sheet-2:
Col-F Col-I Col-J
1234567890 C
1234573245
1234576889 R FedEx 293833849505
1234895498
1234921132 X
1234922374 R UPS 1Z585960604968

Result Should be:
Sheet-1:
Col-F Col-G
1234567890
1234576889 FedEx 293833849505
1234864534
1234895498
1234922374 UPS 1Z585960604968
1234944784