Return another cell if one cell equals another with formula

**Excl3454** · 03-06-2021, 01:09 AM

Hi all,
this my 2nd time in this forum and really had a very good help and experience 1st time

Thank you Fluff13.
I have a question about dragging down formula. when i drag formula it change row numbers but i want same formula for every line.
i want to make I5:N5 equal another value entered in A5:F5 , for any row and column i drag.
Sample attached. Thank you
screenshot.5811.jpg

**FDibbins** · 03-06-2021, 02:45 AM

Not really sure what you are expecting here?

If you want the bolded parts to stay the same when copied across, but to change when copied down...
=IF(A5=1,A1,IF(A5=2,B1,IF(A5...etc=3,C1,IF(A5=4,D1,IF(A5=5,E1,IF(A5=6,F1,"Error"))))))
then try this....
=IF($A5=1,A1,IF($A5=2,B1,IF($A5=3,C1,IF($A5=4,D1,IF($A5=5,E1,IF($A5=6,F1,"Error"))))))

If that wasnt it, plz add some expected outcomes.

**JohnTopley** · 03-06-2021, 03:18 AM

Try

=IFERROR(INDEX($A$1:$F$1,,A5),"")

Copy across and down

I assume the result is a matrix of values in the first row

**kvsrinivasamurthy** · 03-06-2021, 06:35 AM

Not clear what is actually required.
Try this.
In I5 then copied across

=IFERROR(AGGREGATE(15,6,$A$1:$F$1/(A5=$A$5:$F$5),1),"")

**Excl3454** · 03-06-2021, 11:15 AM

Originally Posted by JohnTopley

Try

=IFERROR(INDEX($A$1:$F$1,,A5),"")

Copy across and down

I assume the result is a matrix of values in the first row

Thank you so much , your solution and kvsrinivasamurthy both works what I needed. Many thanks again

**Excl3454** · 03-06-2021, 11:16 AM

Originally Posted by kvsrinivasamurthy

Not clear what is actually required.
Try this.
In I5 then copied across

=IFERROR(AGGREGATE(15,6,$A$1:$F$1/(A5=$A$5:$F$5),1),"")

Very very nice, now i have 2 methods and all working well. that was very nice of yo u. thank you

**Excl3454** · 03-06-2021, 12:47 PM

sorry still cant figure out when I have more than 6 numbers to check,
I copied the code to my worksheet, just changed rows A1:F1 to O11:T11 and needed to check for 49 numbers instant of 6
Can you please take a look at this attachment , row 22, O22:T22 Please?

**JohnTopley** · 03-06-2021, 01:37 PM

The problem is that we originally thought the values were limited to those in row 22

I changed formula in O23 to use INDEX formula,

See attached.

**Excl3454** · 03-06-2021, 03:30 PM

Originally Posted by JohnTopley

The problem is that we originally thought the values were limited to those in row 22

I changed formula in O23 to use INDEX formula,

See attached.

Thank you so much John, very nice and realty appropriate it.

Return another cell if one cell equals another with formula

LinkBack

Thread Tools

Rate This Thread

Display

Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Re: Return another cell if one cell equals another with formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If a cell in a column equals today's date return the value of another cell adjacent to it

[SOLVED] if cell equals value then return answer

[SOLVED] Return entire row if cell equals

[SOLVED] if a cell value in a range equals another cell, then return a value left of a cell

[SOLVED] IF 1 cell equals a range of others, return this cell

if cell equals value then return previous cell address

An equals formula that doesn't return a blank cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions