0

I am trying to find a cell in excel using a formula from a range.

The range will be as such:

╔═══╦═══╦═══╦═══╦═══╦═══╦═══╦═══╗
║   ║ A ║ B ║ C ║ D ║ E ║ F ║ G ║
╠═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣
║ 1 ║ x ║ x ║ x ║ D ║ x ║ x ║ x ║
╚═══╩═══╩═══╩═══╩═══╩═══╩═══╩═══╝

The range value will be dynamic, but it will be the same as the first row of the column it is in. So if the value were placed elsewhere in the range it would be as such:

╔═══╦═══╦═══╦═══╦═══╦═══╦═══╦═══╗
║   ║ A ║ B ║ C ║ D ║ E ║ F ║ G ║
╠═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣
║ 1 ║ x ║ B ║ x ║ x ║ x ║ x ║ x ║
╚═══╩═══╩═══╩═══╩═══╩═══╩═══╩═══╝

The X's represent an #N/A. I have a sum at the end of this, which tells me if the range contains a value, so the formula would be structured:

`=IF(H1= 1,#find the cell and display the value, "")`

H1 would be a column that shows 1 if there is a value in that range, and 0 if not.

I hope this isn't too confusing. Basically need a formula that will search the range for the value and display it. It will only contain one value, but I can't use VLookups or index matches because it's a dynamic value.

Any help would be great, thanks!

0

You can use INDEX/MATCH as an array formula:

=IF(H1=1,INDEX(A1:G1,MATCH(TRUE,NOT(ISERROR(A1:G1)),0)),"")

This is an array formula and needs to be confirmed with Ctrl+Shift+Enter.
If done correctly then Excel will put {} around the formula.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.