IF Function optimisation

[Abbioka]

Dear all,

the attached sheet is currently working well, but for a self improvement, I would like to have some recommendations on how to improve it/make it shorter, and thus have new inspirations for further works.

Sheet explanation :
B2:B4 looks up A2:A4 corresponding to F:F
Puts "," every thousands
links G and H to be "price 1 - price 2"
adds JPY mark if B6 rate is 1.
divides, rounds up and adds USD or EUR mark corresponding on the B6 rates.

The current formula is :

Please Login or Register  to view this content.

I am all open to suggestions.

[Glenn Kennedy]

Maybe this:

=LET(l,G2:G4,h,H2:H4,c,CHOOSE(LOOKUP($B$6,{1,100,121},{1,2,3}),1,$B$6,$B$6),z,LOOKUP($B$6,{1,100,121},{"￥","$","€"}),TEXT(ROUND(l/c,0),"#,##0")&z&" - "&TEXT(ROUND(h/c,0),"#,##0")&z)

[Abbioka]

Hi Glenn,

Thank you for your quick response.

It works and is very shorter, thank you very much for your time.


(edit post does not show the post itself, making it impossible to edit. Could you please turn the post in solved for me?)

[TMS]

One way:

Please Login or Register  to view this content.

[Glenn Kennedy]

Did I miss a bit... are they always in the same order... 1,2,3, in both places?

[Glenn Kennedy]

If the orders are NOT the same:

=LET(a,$B$6,r,G2:H4,l,INDEX(r,MATCH(A2:A4,F2:F4,0),1),h,INDEX(r,MATCH(A2:A4,F2:F4,0),2),c,CHOOSE(LOOKUP(a,{1,100,121},{1,2,3}),1,a,a),z,LOOKUP(a,{1,100,121},{"￥","$","€"}),TEXT(ROUND(l/c,0),"#,##0")&z&" - "&TEXT(ROUND(h/c,0),"#,##0")&z)

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[TMS]

Building on Glenn's solution, combined with mine:

Please Login or Register  to view this content.

[TMS]

Thanks for the rep.