Embedded If statements versus lookup tables

[Davidns]

I am trying to optimize the following and not sure how to do so. I have a list of 50 items. Let's say ten of them need to return a value based on the item in the list whereas the other 40 need to simply return a default value. For example, I have a list of vendors and need a column for the currency used by that vendor. Ten of them are non US currencies and 40 are US $s. If I use If statements, i will have a ridiculously long embedded series of Ifs. And if I used a lookup table, I will have to include every vendor which I don't want to do since the list grows often and is usually just a US $ vendor anyway.
So is there a simple way to return the correct currency if the vendor is one of the ten, and US$s for everyone else?
I suspect there is a simple answer, but somehow not wrapping my head around it. Thanks in advance!!

 

[BrianGGG]

If I understand your problem correctly, you can:
Create a lookup table that just contains the non-US vendors
Use Index/Match to find the currency
If the vendor is not in the list, you can use the IFNA function to set currency to USD when the record does not exist in the lookup table.

Something like the below, lookup table on the left ... columns E and G look up the correct currency:

	B	C	D	E	G
vendor	currency	a	JPY
a	JPY	b	EUR
b	EUR	c	INR
c	INR	d	USD
x	USD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]28[/TD]

[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G28[/TH]
[TD="align: left"]=IFNA(INDEX(t_currency[currency],MATCH(E28,t_currency[vendor],0)),"USD")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Davidns]

Thanks Brian! I love this forum. After using Excel for 25+ years, I did not know about the "IfNA" function, which solves my current problem perfectly. Very grateful for your prompt advice. The only thing I remain confused about is when Index or Match should be used rather than just vlookup. Generally speaking, and in this case in particular, I have a two column table showing Vendor and Currency, so it seems a vlookup formula works more directly than the index match approach. My formula would just be "IFNA(Vlookup(merchant [or cell with the merchant name],2),"USD). I actually have never used Index or Match so perhaps I am missing something and am open to learning a better way if you think I am.
Thanks again!

 

[BrianGGG]

There are hundreds of articles about the differences between VLOOKUP and INDEX(MATCH()). At various times, one is faster than the other, more flexible than the other, is less risky, etc.
At this point, I can't point to one reason why INDEX/Match is better.

All I can say is, when I learned Excel ... I picked Index/Match and stuck with it. I don't even think about it anymore, I just use it as part of muscle memory.
I think that this is really just personal preference at this point for most users.

I pasted a link to a good summary below. Regardless of whether you believe it or not ... my advice is to pick only one method and use it at all times. It is confusing and error-prone to use both interchangably.



http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/



BrianGGG

 

[Davidns]

Excellent. Thank you very much. Seems like great advice! Much appreciated.