indexing - How can I used One Choices of TWO Column for INDEX MATCH Function?

Question

Hi is there a way to used choices with one outcome on index match?

E.g. In ColumnA=Item Number List RangeA1-A100, ColumnB=Item Name List RangeB1-B100, ColumnC=Item Description RangeC1-C100, ColumnD=Item Price RangeD1-D100 (Column A-D are LOCK)

In ColumnF, I would like to input only an ITEM NUMBER or ITEM NAME. In ColumnG(contained formula), is were the description of an item will appear based on the item number or the name that is given at ColumnF. In ColumnH(displays the prices of any given item).

I came up with using this formula, but it can only be used the columnA which is ITEM#, cannot not be used for ColumnB. =INDEX($A$2:$D$100,MATCH($F2,$A$2:$A$100,0),MATCH(G$1,$A$1:$D$1,0))

I tried to used this one but it says to many arguments. =INDEX($A$2:$D$100,MATCH($F2,$A$2:$A$100&$B$2:$B$100),MATCH(G$1,$A$1:$D$1,0))

It is possible to combined the two arguments? This allow me to used either an ITEM# OR ITEM NAME. to search from the range the item description and price.

Thank you for any possible suggestion.

question from:https://stackoverflow.com/questions/65843401/how-can-i-used-one-choices-of-two-column-for-index-match-function

Answer 1

Waitting for answers