Hi Excel experts
I am trying below formula
Please Login or Register to view this content.
and it work fine if my Sheet2 match first value but now i have range of cells , now how to Match the value i had attached the excel
Hi Excel experts
I am trying below formula
Please Login or Register to view this content.
and it work fine if my Sheet2 match first value but now i have range of cells , now how to Match the value i had attached the excel
Last edited by mailtoashok13; 06-08-2021 at 05:26 AM.
MATCH on Column B will return the first value only i.e TESTA, TESTAA
Answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
I had attached the sample excel please look and let me know the result
In F2 then copy down.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thank you for the response but i have to follow below conditions check then Search the result below is my formula with checks it work but it always work if First value matches
I also updated my Excel sory i attached the wrong one row 4 in Sheet1 is updated with expected output "AAAAAAA - GGGGG"Please Login or Register to view this content.
Pl see post #4
This Formula work if it Matches with First one but there is range now in Sheet 2 how to change this formula so it work with range selection ?Like in Column B of Sheet 2
ColumnA ColumnB
XXXX JJJJJJJJJJJ– DDD BBB AA-YYY
LLLLL FFFFFFFF – UUU BBB AA-YYY
XXXX YYYYYYY – DDD BBB AA-YYY
JJJJJJ YYYYYYY – DDD BBB AA-YYY
YYYYYYYY - GGGGG FF -DDD
SSSSSSSSS - BBBBBB FF -DDD
AAAAAAA - GGGGG FF -DDD
YYYYYYYY - GGGGG FF -DDD
YYYYYYYY - BBBBBB FF -DDD
Formula will check For match of D value with Column A and E value with column B. If both matches only result .will be given.
In the formula you see both D2 And E2 are used.
Thanks for your quick response but i have to check few conditions before like IF(AND(A2="OK",B2=C2 then i have to perform check so could you help me in adding above conditions before
In F2
=IF(D2="","", IF(AND(A2="OK",B2=C2),IF(SUMPRODUCT(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,Sheet1!D2))*(Sheet2!$B$2:$B$10=Sheet1!E2))>0,D2,""),""))
Hi i tried this and getting below output in ColumnF expectation to getting match value in ColumnF so if my Match found "AAAAAAA - GGGGGG" it should update Column F with "AAAAAAA - GGGGGG"
Attachment 735686
Your attachment is not opening . Pl upload once again. Pl read yellow banner in the top of this page.
The MATCH work looks for a predefined thing in a scope of cells, and afterward returns the overall situation of that thing in the reach. For instance, assuming the reach A1:A3 contains the qualities 5, 25, and 38, the equation =MATCH(25,A1:A3,0) returns the number 2, since 25 is the second thing in the reach.
attached please check
In F2
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks