Display value on cell according to values on other table/array

[6tel]

Hi guys. It's been a long time...

I recently heard from my hero, the very own mr. SirJB7 (our virtual Martín Fierro), that he has abdicated his way to the throne at Chandoo 2 or 3 years ago... I hope any of you in the line of succession can help me here with a very simple task, because it seems I've forgotten the way how it is done:

I'm having a table with two columns (A and B) showing information on rate and rate type.

I want column B to show the rate type corresponding the value in each of the rows on column A; and this, according the array that I have on columns D and E (this array is the main reference to each rate and its rate type, so these values are the core of the whole thing).

So, the formula in B2 must do this:

It sees what value is in A2, and looks in D1:E20 for the number in column E that is equal to. After finding it, it lists the rate type corresponding the rate (so it must display that it corresponds to "COR_GCIA")

Any help would be greatly appreciated.

Greetings.

 

[NARAYANK991]

Hi ,

Something like this ?

Narayan

 

[6tel]

Exactly like it!!

Many thanks, Narayank991!!!

 

[6tel]

Hola once again, Narayan.

Sorry for something else... Is there a way to make the formula return just a blank cell in B2 if the value in A2 is zero or different to those listed in column E at the D1:E20 array?

 

[NARAYANK991]

Hi ,

See the attached file.

Column B shows the formula which will work in all Excel versions ; column C uses the IFERROR function , which will not work in Excel 2003 and earlier versions.

Narayan

 

[6tel]

Thanks, dear Narayank991... I know it's been ages (I forgot about this pending topic I had here).

 

[Peter Bartholomew]

@6tel
The problem with revisiting topics from years gone by is that, for many, everything has changed! Now I would use
= XLOOKUP(Rate, Index, Rate_type, "")