Excel Magic Trick 1235: 2-Way Lookup For Currency Conversion: Don’t Use IF, Use INDEX or VLOOKUP

hi mike what if i want set my own customize rates stil it will work?

Love your Enthusiasm

D rat

As usual, you are a genius. Thanks

How to convert in specific dates....all different date?

Thank you Sir for enlighten us, God bless you. Your tutorials are amazing and your way of teaching is very details and clear. Appreciate and thank you so much.

Thanks, you solved my problem with historical exchange rates!

OMG I CAN LISTEN FOR EVER!!! I LOVE HOW FUN U MAKE IT!!!!! <3

Wow! Thanks man! Amazaing vid! <3

I am learning excel though all of your videos and it's been really helpful..
I have excel test for an IJP, so is there a link or video related to a excel test which include all kinds of questions?

I am using MarketXLS and it's great.

Hi, What if i want to insert the data from cell A4 to cell J5. Saying id source currency is CAD and Target Currency is GBP, then, put a value from cell A4 into cell J5. how should i go about it.?

Please help.

Thanks a bunch, Mike. All your videos are excellent. Very thorough explanation. Very clear demonstration. Very helpful downloadable files. And you even provide answer files. Really indebted to you.

I had to use similar tricks with reports before. Definitely worth noting.

+Hussein Korish , cool - so many fun ways!

+Excel Stuff (Felipe Vaz) , Glad you like them!

awesome video!! ... you can also use offset (starting at G3) in the same way index was used ... keep doing these videos!!

Hey Mike! Thank for making this video (y)

Nice one Excelisfun!

Nice way to look up. But where are the currency-signs? £ 333.08, CA$ 554.75, € 845.19. Thanks! And greetings from the netherlands.

sorry about my question here
i heard that the if-error fn. take more time to process so iam replaced it in my file and i think no difference
my old formula is
=IFERROR(IF(MATCH([@الطرحة];'الطرحة المقابلة'!A:A;0)>1;"موجود");IF([@الطرحة]=[@طول];"";"صنف جديد"))
my new one is
=IF(ISNA(MATCH([@الطرحة];'الطرحة المقابلة'!A:A;0));IF(ISBLANK([@الطرحة]);"";"صنف جديد");"موجود")
pls provide me if the new one is more complex or take more time to proceed
Best regards

For the VLOOKUP and MATCH: Can you use VLOOKUP and HLOOKUP together instead? We have rarely seen the HLOOKUP in action here?

Wow ... that was amazing ... i only used to solve it just by using array ( =Sum($H$4:$L$8*($H$3:$L$3=B4)*(($G$4:$G$8=C4))*A4 ... then CSE

Great video! INDEX & MATCH is about the only lookup function you'll ever need but still the only lookup function most of the people seem to know is VLOOKUP. Also love the Bill Szysz 2-way lookup by summing solution. People that smart makes me scary... The only other ways I could've tried to solve this would've been tweaking the headers a bit & use the INTERSECT-function or put the whole table into Power Query to unpivot it which would've opened a new world of possibilities to solve this: How about a video about GETPIVOTDATA-function, Mike? That's my favorite function to use when I'm doing dashboards.

Interesting video, the topic got me thinking that can you populate the conversion table with current values, so it would populate with that day's exchange rate automatically? i.e. scrap the information from a website and automatically update. Marcus

Thanks for lesson about probably the most important functions in excel (INDEX, MATCH - of course, this is my subjective opinion) :-)
Only for fun - here is 2-way lookup by summing ;-))
=SUM((B4&C4=$H$3:$L$3 & $G$4:$G$8)*$H$4:$L$8)*A4
CTRL+SHIFT+ENTER

Wow, that IF was cool ! :) :)

@Francis Osuna, You are welcome!