Finding Data in Separate Sheets and Transferring a Specific Value

[clayest94]

Hi all,

I'm new here so go easy on me! I've got two data sets that have the same data on them, in different orders. Here's what I'm looking to do:

I need an if/then function, but I'm not familiar with them. What I need to do is say if D7:G7 (Origin City, Origin State, Destination City, Destination State) on Sheet 1 equals O2:R2 (Origin City, Origin State, Destination City, Destination State) on Sheet 2 are the same, then copy the value from D2 on Sheet 2 to I7 on Sheet 1.

I'm sure this is probably a pretty common function, but I'm just not familiar. Any and all help is appreciated!

Thanks!
--E

 

[Toadstool]

Hi Clayest94,

Here is Sheet2 in column D sequence

Clayest94.xlsx
	D	E									N	O	P	Q	R
1	Rate											Origin City	Origin State	Destination City	Destination State
2	652											Lancaster	PA	Jersey City	NJ
3	666											Elgin	IL	Germantown	MD
4	688											Kalamazoo	MI	Tacoma	WA
5	767											Hammond	IN	Akron	OH
6	812											San Antonio	TX	Springfield	IL
7	888											Albany	GA	Mobile	AL
8	889											Cleveland	OH	Stockton	CA
9	900											Lancaster	PA	Cape Coral	FL
10	922											Baton Rouge	LA	Troy	MI
11	1200											Los Angeles	CA	Dallas	TX
12	1211											Muskegon	MI	Greenville	NC
13	1322											Bowling Green	KY	Killeen	TX
14	1455											Scranton	PA	Huntsville	AL
15	2100											Bristol	VA	Fort Wayne	IN
16	2211											Sacramento	CA	Gulfport	MS
17
Sheet2

Here is Sheet1 with the retrieval formula

Clayest94.xlsx
	D	E	F	G	H	I
1	Origin City	Origin State	Destination City	Destination State		Rate
2	Los Angeles	CA	Dallas	TX		1200
3	Lancaster	PA	Jersey City	NJ		652
4	San Antonio	TX	Springfield	IL		812
5	Cleveland	OH	Stockton	CA		889
6	Hammond	IN	Akron	OH		767
7	Lancaster	PA	Cape Coral	FL		900
8	Elgin	IL	Germantown	MD		666
9	Baton Rouge	LA	Troy	MI		922
10	Albany	GA	Mobile	AL		888
11	Muskegon	MI	Greenville	NC		1211
12	Kalamazoo	MI	Tacoma	WA		688
13	Bowling Green	KY	Killeen	TX		1322
14	Scranton	PA	Huntsville	AL		1455
15	Bristol	VA	Fort Wayne	IN		2100
16	Sacramento	CA	Gulfport	MS		2211
Sheet1

Cell Formulas
Range	Formula
I2:I16	I2	=IFERROR(INDEX(Sheet2!$D$2:$D$9999,MATCH(1,INDEX((Sheet2!$O$2:$O$9999=D2)*(Sheet2!$P$2:$P$9999=E2)*(Sheet2!$Q$2:$Q$9999=F2)*(Sheet2!$R$2:$R$9999=G2),0,1),0)),"No match")

 

[Radoslaw Poprawski]

What I need to do is say if D7:G7 (Origin City, Origin State, Destination City, Destination State) on Sheet 1 equals O2:R2 (Origin City, Origin State, Destination City, Destination State) on Sheet 2 are the same, then copy the value from D2 on Sheet 2 to I7 on Sheet 1.

Wont that create duplicated entries in sheet1?
howeever,
if you have sheet1 and sheet2 and you wish to find records (rows) that are in both sheets. then Power query would do it easily

just provide data sample to work on.

 

[Toadstool]

Wont that create duplicated entries in sheet1?

Only if there's duplicates in column D of Sheet2 or duplicate entries of D&E&F&G in Sheet1.

 

[Radoslaw Poprawski]

I had to read this few times.
anyway, isn't that gr8 usage of powerquery then play with formulas?

 

[Toadstool]

isn't that gr8 usage of powerquery then play with formulas?

Not unless you've downloaded and installed the Power Query COM addin.

...and I don't 'play' with formulas. I construct bespoke formulaic solutions