is there a way to link 2 columns together

[SPstudent]

Hi,

I have two columns, one representing days and the other prices.
Is there a way I can link these two columns such that when I want to retrieve the value of a specific price and paste it to a different column, the days linked to that price will be automatically retrieved too?

[oeldere]

You get better help if you post an Excel-example of your workbook, without confidential information.
Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.
Use BEFORE/AFTER sheets if that helps make it clearer.

[SPstudent]

Double posted.

[SPstudent]

Thanks for the advice. Maybe something like this?
Attachment 172509
What I want to do is take for example when I retrieve the highest price value (i.e. 36.5), I would also like to automatically retrieve "Day 7" in column A and paste it to column E and F (Day 7 and 36.5 respectively) for instance.

[oeldere]

Please sent the excel-file, so we can test the result.

[benishiryo]

hi SPstudent (singapore poly?), welcome to the forum. is the amounts unique? appearing just once in the list? if so, you can try this in E1 with the amount in F1:
=INDEX(A1:A18,MATCH(H1,B1:B18,0))

[SPstudent]

hi SPstudent (singapore poly?), welcome to the forum. is the amounts unique? appearing just once in the list? if so, you can try this in E1 with the amount in F1:
=INDEX(A1:A18,MATCH(H1,B1:B18,0))

Hi, yea singapore poly. Thank you! Erm the amounts are not unique, they are automatically generated. I'm quite new to VBA so what do you mean by =INDEX?

[benishiryo]

ahhh i see~ INDEX is actually just a formula, nothing to do with VBA. it usually goes along with the MATCH formula. the 1st field "A1:A18" is where the results you want are (the Day number). the 2nd field is to put in the row number. by eyeballing, we can see that it is in row 7. but we want Excel to find that for us, so we use the MATCH formula to return the row number.

and as oeldere mentioned, it's actually easier for us if you upload an Excel file instead of a picture. but i did one for you with 2 solutions inside. Sheet1 is using pivot table. highlight A1:A19, go to Insert Pivot Table -> OK. place Amt in "Report Filter" & "Values". place Day in Row labels. Filter the Amt "36.5" like i did, and you should all the Day that has this amount.

in Sheet 2, i used a more complex formula to get multiple results from the INDEX formula. you can keep copying the formula until you get an error. that means it has no other results that contains 36.5

[SPstudent]

Alright thanks alot ben! Appreciate the help! I think I got it already.