Multiple Criteria for Dynamic Range

[jasperhuang93]

My spreadsheet has a yield curve table - a column with "terms" (column A) and a column with "PV01s" (column B) - the table resides on the 'SENSITIVITIES_PV01' sheet.

I would like to select column A and search for terms that contain "STSCB", "IRF", "IRS", and terms that begin with an integer.

I would then like to return the corresponding PV01 data from column B into range [F5:F7], [F8:F19], [F20:F35], and [N5:N25] respectively, on another sheet, 'PNL_DATA'.

I have zero idea how to code something so complex, so unfortunately I have nothing any help would be LIFESAVING

[dilipandey]

Hi Jasperhuang93,

Welcome to the forum.

Would suggest you to please upload a sample workbook with dummy data. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[jasperhuang93]

Hi Dilipandey,

I have done as suggested and attached a file with dummy data, thanks.

[jasperhuang93]

Please help! Urgent

[dilipandey]

Hi Jasperhuang93,

Try using below array formula:-

Please Login or Register  to view this content.

See attached;- Sample(16).xls

Regards,
DILIPandey

<click on below 'star' if this helps>

[jasperhuang93]

Hi, in your sample document, the range with the formula contains "#NAME?" errors?

[dilipandey]

There is no error, see the screenshot image attached which clarifies this

No # Name Error.jpg

Regards,
DILIPandey

<click on below 'star' if this helps>

[jasperhuang93]

Hm.. how strange, I am gettign an error on my machine, are there any add-ins or anything I may not have checked off?

[dilipandey]

There is no add-in

Check if you are able to apply some other formulas like sum, count etc
Check if your workbook is in auto-calculate mode

Consult your local IT helpdesk, at last

Regards,
DILIPandey

<click on below 'star' if this helps>

[Marcol]

Your file and profile indicate you are using 2003.

IFERROR() will not work for you you will need IF(ISERROR(),,)

IFERROR() will return #NAME? error with 2003

[jasperhuang93]

Would this be the correct formula using IF(ISERROR(),,)?

Please Login or Register  to view this content.

[Marcol]

I'm not clear as to what you are trying to achieve, but try ...
In F5

Please Login or Register  to view this content.

You might need to change the red 0

If this is correct then you don't need Column A, you could use this ...

Please Login or Register  to view this content.

[jasperhuang93]

@Marcol, works! Thanks a bunch

[Marcol]

Hmm...?
I'm not sure that it does!

See the attached workbook.

In Sheet("SENSITIVITIES_PV01") C1

Please Login or Register  to view this content.

Drag/Fill Down
This returns the second part of the codes beginning "STSCD","IRF", and "IRS" and codes beginning with an integer.

Then in Sheet("PNL_DATA") F5

Please Login or Register  to view this content.

Drag/Fill Down

This returns more matches than my initial reading of your problem does, only you can decide what is correct.