Printing the adjacent value of matched cell

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello Sir,


I was stuck into finding the value of matched column next cell value,


Here is what my question actually is

Hello,
I have 2 columns in sheet A,I have another 2 columns in sheet 2
I wanted to match Sheet 1:cell1 with sheet2:cell(1 to N)
and sheet1:cell2 with sheet2:cell(1-N) (only if both conditions are true) then do
and if matched will display what ever is present in sheet2:cell(Matched column) in sheet 1:E1
I am unsure to what to add value of match string
I have used the formula below
=IF(B20=Sheet2!$D$1:$D$100, IF( C20=Sheet2!$E$1:$E$100,Sheet2!$F$1:$F$100,G20),G20)
But its prinitng right data if data matches in the same numbered column in both the sheets.
Means its printing Sheet2:C3 (if Sheet1!A1=Sheet2:A1 and Sheet1!B1=Sheet2:B1)
and not printing the matched records based on finding column in sheet2.

I would be nice if u shed some knowledge on the Same,

Thanks in Advance



2 replies

Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi sachin,

I'm having trouble understanding you.
Could you provide a small data sample and re-explain what you want to see happen.

You can also upload your file using a site like ww.speedyshare.com.

Best regards,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You question leaves a lot of questions in mind

What if one sheet 2, the first match is on row 2 and the 2nd match is on row 5, in that case what goes to E ?
Based on your statement "and sheet1:cell2 with sheet2:cell(1-N) (only if both conditions are true) then do
and if matched will display what ever is present in sheet2:cell(Matched column) in sheet 1:E1 " I too am unable to see what you are trying to ask.

Also how many rows are there in both sheets, any lookup/match will make performance an issue. So if you have lot of rows, you may want to think about your sheets layout