How to use index and match when you have 2 values foundExcel compare two columns and highlight when foundSimple Pivot Table to Count Unique ValuesSum up similar values in VLOOKUP/INDEX-MATCH excel functionsExcel index match multiple row resultsIndex & Match formula does not show the repeated entry with different valueClick on formula cell that uses match and index to link / jump to source cellUsing Index with MATCHExcel column with duplicate values, return index value another columnUsing array with index matchHow to compare or Match values in 10 different columns on the same row
is it biologically possible for a creature that can be compatible to reproduce with any creature?
Why apt asking to uninstall GIMP when installing ardour?
What exactly is meant by "partial function" in functional programming?
Image Manipulation Software That Is Extendable With Custom Filters
Should I respond to a sabotage accusation e-mail at work?
Which person is telling the truth?
LTSpice Zener diode bug?
Would a spacecraft carry arc welding supplies?
Options for passes to national parks in Arizona/Utah for 5 people travelling in one car
Given a Fibonacci number , find the next Fibonacci number
Are there any Baryons that have quark-antiquark combinations?
Why didn't Aboriginal Australians discover agriculture?
Was Hitler exclaiming "Heil Hitler!" himself when saluting?
As a vegetarian, how can I deal with microwaves smelling of meat and fish?
Automatically creating table with borders
Where are ms paint icons stored?
Probability of a 500 year flood occuring in the next 100 years - comparison of approaches
Can I reproduce this in Latex
How to prevent password reset from disclosing private email addresses?
What is an informed evaluation of resource availability?
If equal temperament divides octave into 12 equal parts, why hertz differences are not the same but element 12th of two?
How does an Evocation Wizard's Overchannel ability interact with Chaos Bolt?
Stare long enough and you will have found the answer
What can I wear to avoid getting frisked and crotch searched by TSA at the airport?
How to use index and match when you have 2 values found
Excel compare two columns and highlight when foundSimple Pivot Table to Count Unique ValuesSum up similar values in VLOOKUP/INDEX-MATCH excel functionsExcel index match multiple row resultsIndex & Match formula does not show the repeated entry with different valueClick on formula cell that uses match and index to link / jump to source cellUsing Index with MATCHExcel column with duplicate values, return index value another columnUsing array with index matchHow to compare or Match values in 10 different columns on the same row
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;
I have 2 tables: one for consults(table 1) and other as a data base(table 2).
Both tables have a "Reference" column. I wish to use the value in the "Reference" column in the table 1 to find a value in the "Reference" column in the table 2.
The table 2 has 2 more columns : "1st appearing result" and "final result". Once found the "reference" value in the table 2, I want to receive the "final result". If "final result" is empty, I want to recieve the "1st appearing result".
The real problem is that I have multiple rows in the table 2 with the same value for "Reference". Also, I have to do that without any auxiliary column.
I'll attach a pic that might help to explain.
Thanks in advance
I've tried to use lookV and index with match, but it would fly because of the multiple rows with the same value of reference.
excel excel-formula
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
add a comment
|
I have 2 tables: one for consults(table 1) and other as a data base(table 2).
Both tables have a "Reference" column. I wish to use the value in the "Reference" column in the table 1 to find a value in the "Reference" column in the table 2.
The table 2 has 2 more columns : "1st appearing result" and "final result". Once found the "reference" value in the table 2, I want to receive the "final result". If "final result" is empty, I want to recieve the "1st appearing result".
The real problem is that I have multiple rows in the table 2 with the same value for "Reference". Also, I have to do that without any auxiliary column.
I'll attach a pic that might help to explain.
Thanks in advance
I've tried to use lookV and index with match, but it would fly because of the multiple rows with the same value of reference.
excel excel-formula
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
add a comment
|
I have 2 tables: one for consults(table 1) and other as a data base(table 2).
Both tables have a "Reference" column. I wish to use the value in the "Reference" column in the table 1 to find a value in the "Reference" column in the table 2.
The table 2 has 2 more columns : "1st appearing result" and "final result". Once found the "reference" value in the table 2, I want to receive the "final result". If "final result" is empty, I want to recieve the "1st appearing result".
The real problem is that I have multiple rows in the table 2 with the same value for "Reference". Also, I have to do that without any auxiliary column.
I'll attach a pic that might help to explain.
Thanks in advance
I've tried to use lookV and index with match, but it would fly because of the multiple rows with the same value of reference.
excel excel-formula
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
I have 2 tables: one for consults(table 1) and other as a data base(table 2).
Both tables have a "Reference" column. I wish to use the value in the "Reference" column in the table 1 to find a value in the "Reference" column in the table 2.
The table 2 has 2 more columns : "1st appearing result" and "final result". Once found the "reference" value in the table 2, I want to receive the "final result". If "final result" is empty, I want to recieve the "1st appearing result".
The real problem is that I have multiple rows in the table 2 with the same value for "Reference". Also, I have to do that without any auxiliary column.
I'll attach a pic that might help to explain.
Thanks in advance
I've tried to use lookV and index with match, but it would fly because of the multiple rows with the same value of reference.
excel excel-formula
excel excel-formula
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
edited Mar 28 at 21:35
Scott Craner
104k8 gold badges28 silver badges54 bronze badges
104k8 gold badges28 silver badges54 bronze badges
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
asked Mar 28 at 21:32
Leandro CastelaniLeandro Castelani
51 bronze badge
51 bronze badge
add a comment
|
add a comment
|
1 Answer
1
active
oldest
votes
What about a UDF? Not the most dynamic solution, but it would work for a static worksheet setup like in your example:
Function RETURNRESULT(refrng1 As Range, refrng2 As Range) As String
Dim cell As Range
For Each cell In refrng2
If refrng1 = cell Then
If cell.Offset(0, 7).Value <> "" Then
RETURNRESULT = cell.Offset(0, 7).Value
Exit For
ElseIf cell.Offset(0, 6).Value <> "" And finalresult = False Then
RETURNRESULT = cell.Offset(0, 6).Value
End If
End If
Next cell
End Function
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
That is great!!
– Leandro Castelani
Mar 29 at 1:32
add a comment
|
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55407166%2fhow-to-use-index-and-match-when-you-have-2-values-found%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
What about a UDF? Not the most dynamic solution, but it would work for a static worksheet setup like in your example:
Function RETURNRESULT(refrng1 As Range, refrng2 As Range) As String
Dim cell As Range
For Each cell In refrng2
If refrng1 = cell Then
If cell.Offset(0, 7).Value <> "" Then
RETURNRESULT = cell.Offset(0, 7).Value
Exit For
ElseIf cell.Offset(0, 6).Value <> "" And finalresult = False Then
RETURNRESULT = cell.Offset(0, 6).Value
End If
End If
Next cell
End Function
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
That is great!!
– Leandro Castelani
Mar 29 at 1:32
add a comment
|
What about a UDF? Not the most dynamic solution, but it would work for a static worksheet setup like in your example:
Function RETURNRESULT(refrng1 As Range, refrng2 As Range) As String
Dim cell As Range
For Each cell In refrng2
If refrng1 = cell Then
If cell.Offset(0, 7).Value <> "" Then
RETURNRESULT = cell.Offset(0, 7).Value
Exit For
ElseIf cell.Offset(0, 6).Value <> "" And finalresult = False Then
RETURNRESULT = cell.Offset(0, 6).Value
End If
End If
Next cell
End Function
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
That is great!!
– Leandro Castelani
Mar 29 at 1:32
add a comment
|
What about a UDF? Not the most dynamic solution, but it would work for a static worksheet setup like in your example:
Function RETURNRESULT(refrng1 As Range, refrng2 As Range) As String
Dim cell As Range
For Each cell In refrng2
If refrng1 = cell Then
If cell.Offset(0, 7).Value <> "" Then
RETURNRESULT = cell.Offset(0, 7).Value
Exit For
ElseIf cell.Offset(0, 6).Value <> "" And finalresult = False Then
RETURNRESULT = cell.Offset(0, 6).Value
End If
End If
Next cell
End Function
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
What about a UDF? Not the most dynamic solution, but it would work for a static worksheet setup like in your example:
Function RETURNRESULT(refrng1 As Range, refrng2 As Range) As String
Dim cell As Range
For Each cell In refrng2
If refrng1 = cell Then
If cell.Offset(0, 7).Value <> "" Then
RETURNRESULT = cell.Offset(0, 7).Value
Exit For
ElseIf cell.Offset(0, 6).Value <> "" And finalresult = False Then
RETURNRESULT = cell.Offset(0, 6).Value
End If
End If
Next cell
End Function
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
answered Mar 28 at 22:15
dwironydwirony
5,0613 gold badges14 silver badges35 bronze badges
5,0613 gold badges14 silver badges35 bronze badges
That is great!!
– Leandro Castelani
Mar 29 at 1:32
add a comment
|
That is great!!
– Leandro Castelani
Mar 29 at 1:32
That is great!!
– Leandro Castelani
Mar 29 at 1:32
That is great!!
– Leandro Castelani
Mar 29 at 1:32
add a comment
|
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55407166%2fhow-to-use-index-and-match-when-you-have-2-values-found%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
