Need some assistance with replacing contents of a cell with another based on search result

[Harkin]

Hi,

I'm hoping that someone can help me, as I'm fairly new with Excel.

My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.

I'll try to be as clear as possible:

tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row

All cells are text values - some digits, but should be treated as a regular String.

The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.

Please let me know if you have questions. I can write up a specific example if it would help as well.

[FDibbins]

Hi and welcome to the forum

It would have been easier if you provided sample data, but try this...

Sheet 1

	A	B	C
1	aa1	bb1	cc1
2	aa2	bb2	cc2
3	aa3	bb3	cc3
4	aa4	bb4	cc4
5	aa5	bb5	cc5
6	aa6	bb6	cc6
7	aa7	bb7	cc7
8	aa8	bb8	cc8
9	aa9	bb9	cc9
10	aa10	bb10	cc10

Sheet 2

	C	D	E
1	aa1	bb1	cc1
2	aa2	bb2	cc2
3	aa3	bb3	cc3
4	aa4	bb4	cc4
5	aa5	bb5	cc5
6	aa6	bb6	cc6
7	aa7	bb7	cc7
8	aa8	bb8	cc8
9	aa9	bb9	cc9
10	aa10	bb10	cc10

In sheet 1 C1, copy this ARRAY formula down...
=INDEX('Sheet 2'!$E$1:$E$100,MATCH('Sheet 1'!A1&'Sheet 1'!B1,'Sheet 2'!$C$1:$C$100&'Sheet 2'!$D$1:$D$100,0))...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.