LinkBack URL
About LinkBacks
Dear All,
I need to match 3 value from sheet1 and if the same 3 value found in sheet2 than I need the next cell information, please find the attached file for more detail.
Thanking you in advance
Mubeen
Dear All,
I need to match 3 value from sheet1 and if the same 3 value found in sheet2 than I need the next cell information, please find the attached file for more detail.
Thanking you in advance
Mubeen
Hello
Greetings for the day!
I have done what you needed....
For this I have added two helper columns ..one in both the sheets...
Tell me if it works right….
Hope it helps
Do ask for any other query you may have…
If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...
Regards
Sourabh Gupta
No dear,
I need to bring the sheet2 value if from sheet1 cell
lets say in sheet1 I,J,K,L matching with sheet2 than bring m from sheet2 cell m to sheet1 in m ( just like velookup but for multiple values
Check the example
Never use Merged Cells in Excel
I bring from sheet2 column M to sheet1 column M.
Thanks Zbor,
this what exactly what I need
Re: Formula to match multiple cells in 2 sheets
Thanks Zbor,
this what exactly what I need
Thanks Zbor,
this what exactly what I need
Hello
Yes I have done the same...
Please test by putting some values .....if I,J,K,L columns would match in sheet 2 then value from sheet 2 will come to column m in sheet 1...
It is working fine here...
please check...
Regards
Sourabh
Hello
Is my method not working?
Please reply..
thanks..
Dear Sourabh Gupta ,
Thnks for your support but what exactly I need solution given by Zbor please check his attached file , I think I failed to explain u what I need.
Mubeen
M2=IFERROR(INDEX(Sheet2!$M$7:$M$2382,MATCH(1,INDEX((Sheet2!$C$7:$C$2382=$I2)*(Sheet2!$G$7:$G$2382=$J2)*(Sheet2!$F$7:$F$2382=$L2)*(Sheet2!$H$7:$H$2382=$K2),0),0)),"")
OR
M2=SUMIFS(Sheet2!$M$7:$M$2382,Sheet2!$C$7:$C$2382,$I2,Sheet2!$G$7:$G$2382,$J2,Sheet2!$F$7:$F$2382,$L2,Sheet2!$H$7:$H$2382,$K2)
TRY THIS AND COPY TOWARDS DOWN
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
M2=IFERROR(INDEX(Sheet2!$M$7:$M$2382,MATCH(1,INDEX((Sheet2!$C$7:$C$2382=$I2)*(Sheet2!$G$7:$G$2382=$J2)*(Sheet2!$F$7:$F$2382=$L2)*(Sheet2!$H$7:$H$2382=$K2),0),0)),"")
OR
M2=SUMIFS(Sheet2!$M$7:$M$2382,Sheet2!$C$7:$C$2382,$I2,Sheet2!$G$7:$G$2382,$J2,Sheet2!$F$7:$F$2382,$L2,Sheet2!$H$7:$H$2382,$K2)
TRY THIS AND COPY TOWARDS DOWN
sourabhg98,
your formula work too.
However, if you want to go with helper column I would suggest to use one one (on sheet2).
So your formula would be:
Formula:
=INDEX(Sheet2!M:M, MATCH(I2&","&J2&","&K2&","&L2,Sheet2!Q:Q,0))
Also, optionaly, it's better to use non-standard delimiters (that I didn't include in mine solution since it's unlikely to happend but it would be better).
So instead of , or . or space etc. use something like | ~ ^ etc.
Formula:=INDEX(Sheet2!M:M, MATCH(I2&"^"&J2&"^"&K2&"^"&L2,Sheet2!Q:Q,0))
Here is my way you can consider.
Formula:=INDEX(Sheet2!$M$6:$M$2382,MATCH(4,MMULT(--(Sheet2!$C$6:$F$2382=$I2:$L2),TRANSPOSE(COLUMN($I$2:$L$2)^0)),0))
I have changed the column sequence in the sheet2 to favor formula.
I have explained this technique here. So you can learn if you want.
Searching a whole Row in the table to return Row number or Corresponding cell
Check the attached file:-
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Dear Zbor
thanks for your suggestion..I will keep them in mind next time...
Regards
Sourabh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks