Styjun

What's the story to "WotC gave up on fixing Polymorph"?

Where does an unaligned creature's soul go after death?

Allocating credit card points

How can I repair this gas leak on my new range? Teflon tape isn't working

Is it possible to encode a message in such a way that can only be read by someone or something capable of seeing into the very near future?

Why is the missed-approach course for the "RNAV (GNSS) - A" approach to runway 28 at ENSB shaped all funny?

What are these pixel-level discolored specks? How can I fix it?

What is the need of methods like GET and POST in the HTTP protocol?

Writing a letter of recommendation for a mediocre student

Why does rhenium get a pass on being radioactive?

A high quality contribution but an annoying error is present in my published article

Is it possible to constructively prove that every quaternion has a square root?

Safely hang a mirror that does not have hooks

How to make interviewee comfortable interviewing in lounge chairs

What is the lowest voltage that a microcontroller can successfully read on the analog pin?

How to manage expenditure when billing cycles and paycheck cycles are not aligned?

Idiom for "I came, I saw, I ate" (or drank)

Was there a trial by combat between a man and a dog in medieval France?

The quicker I go up, the sooner I’ll go down - Riddle

Is it a good idea to leave minor world details to the reader's imagination?

Hilbert's hotel, why can't I repeat it infinitely many times?

How does IBM's 53-bit quantum computer compare to classical ones for cryptanalytic tasks?

What are the benefits and disadvantages if a creature has multiple tails, e.g., Kyuubi or Nekomata?

My 15 year old son is gay. How do I express my feelings about this?

How to search a string-defined range for a value?

How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?Check if Cell value exists in Column, and then get the value of the NEXT CellHow to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loopsChecking if a value exists anywhere in range in ExcelSearch String in large Range, return row numberSearch for value in column ExcelExcel - find values in a column except for value in current rowSearch, Match & Return location using Excel?Using VBA to search a range & return values of row to userformExcel: How to return a unique list array as a result of a two column lookup

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

Here is what I am trying to accomplish:

1. Search a column for a match, value = a




2. Return the row of that match -> row(a)




3. Search in row(a) for a match, value = b




4. Return the column of that match -> col(b)




5. Return the cell value at location: row(a),cell(b)

So far, I am able to do steps 1/2 with this:

 =MATCH("a",P$1:$P$100,0)

which returns the row that "a" is found in

However I am running into difficulty searching in row(a) for the column that matches the location of b.

What can I do to fix this?

P.S. for step 5, I can use this given col(b) = 10

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),10)

excel excel-formula

share|improve this question

asked Mar 28 at 15:59

abcdefg12345abcdefg12345

3766 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  is b a header in the first row, because as you have it the return would either be an error or b
  
  – Scott Craner
  Mar 28 at 16:08
  

  
  
  
  

add a comment
 | 

0

Here is what I am trying to accomplish:

1. Search a column for a match, value = a




2. Return the row of that match -> row(a)




3. Search in row(a) for a match, value = b




4. Return the column of that match -> col(b)




5. Return the cell value at location: row(a),cell(b)

So far, I am able to do steps 1/2 with this:

 =MATCH("a",P$1:$P$100,0)

which returns the row that "a" is found in

However I am running into difficulty searching in row(a) for the column that matches the location of b.

What can I do to fix this?

P.S. for step 5, I can use this given col(b) = 10

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),10)

excel excel-formula

share|improve this question

asked Mar 28 at 15:59

abcdefg12345abcdefg12345

3766 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  is b a header in the first row, because as you have it the return would either be an error or b
  
  – Scott Craner
  Mar 28 at 16:08
  

  
  
  
  

add a comment
 | 

Here is what I am trying to accomplish:

1. Search a column for a match, value = a




2. Return the row of that match -> row(a)




3. Search in row(a) for a match, value = b




4. Return the column of that match -> col(b)




5. Return the cell value at location: row(a),cell(b)

So far, I am able to do steps 1/2 with this:

 =MATCH("a",P$1:$P$100,0)

which returns the row that "a" is found in

However I am running into difficulty searching in row(a) for the column that matches the location of b.

What can I do to fix this?

P.S. for step 5, I can use this given col(b) = 10

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),10)

excel excel-formula

share|improve this question

asked Mar 28 at 15:59

abcdefg12345abcdefg12345

3766 bronze badges

 =MATCH("a",P$1:$P$100,0)

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),10)

share|improve this question

asked Mar 28 at 15:59

abcdefg12345abcdefg12345

3766 bronze badges

share|improve this question

asked Mar 28 at 15:59

abcdefg12345abcdefg12345

3766 bronze badges

asked Mar 28 at 15:59

abcdefg12345abcdefg12345

3766 bronze badges

asked Mar 28 at 15:59

abcdefg12345abcdefg12345

3766 bronze badges

1 Answer
1

active

oldest

votes

1

You want to add another match to the INDEX

If the value b is a header to find in the first row:

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",$A$1:$O$1,0))

If the value b is in the same row:

=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),0),0))

The upper will return the value from the row where a is found in column P and b is found in the first row.

The lower will return b or an error depending on whether b is found in the same row that a is found in Column P

share|improve this answer

answered Mar 28 at 16:12

Scott CranerScott Craner

102k88 gold badges2828 silver badges5454 bronze badges

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55402024%2fhow-to-search-a-string-defined-range-for-a-value%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You want to add another match to the INDEX

If the value b is a header to find in the first row:

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",$A$1:$O$1,0))

If the value b is in the same row:

=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),0),0))

The upper will return the value from the row where a is found in column P and b is found in the first row.

The lower will return b or an error depending on whether b is found in the same row that a is found in Column P

share|improve this answer

answered Mar 28 at 16:12

Scott CranerScott Craner

102k88 gold badges2828 silver badges5454 bronze badges

add a comment
 | 

1

You want to add another match to the INDEX

If the value b is a header to find in the first row:

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",$A$1:$O$1,0))

If the value b is in the same row:

=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),0),0))

The upper will return the value from the row where a is found in column P and b is found in the first row.

The lower will return b or an error depending on whether b is found in the same row that a is found in Column P

share|improve this answer

answered Mar 28 at 16:12

Scott CranerScott Craner

102k88 gold badges2828 silver badges5454 bronze badges

add a comment
 | 

You want to add another match to the INDEX

If the value b is a header to find in the first row:

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",$A$1:$O$1,0))

If the value b is in the same row:

=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),0),0))

The upper will return the value from the row where a is found in column P and b is found in the first row.

The lower will return b or an error depending on whether b is found in the same row that a is found in Column P

share|improve this answer

answered Mar 28 at 16:12

Scott CranerScott Craner

102k88 gold badges2828 silver badges5454 bronze badges

 =INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",$A$1:$O$1,0))

=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),MATCH("b",=INDEX($A$1:$O$100,MATCH("a",P$1:$P$100,0),0),0))

share|improve this answer

answered Mar 28 at 16:12

Scott CranerScott Craner

102k88 gold badges2828 silver badges5454 bronze badges

answered Mar 28 at 16:12

Scott CranerScott Craner

102k88 gold badges2828 silver badges5454 bronze badges

answered Mar 28 at 16:12

Scott CranerScott Craner

102k88 gold badges2828 silver badges5454 bronze badges