Excel function to select two columns based on range

[akshay8530]

Hi,

Can you help me with an function to select two columns based on the header and the variable given.

Sample excel file attached for your ref.

Excel sample.JPG

Also Kindly find the excel file in which I am looking for an formula which will look up variable in Col A for Eg USD and search the same in Row 1 and then will select COL D:E and so forth for other currency.

Thanks

[Olly]

It's not clear what you actually want to do with the range - but here's an example of using offset and match to determine the range you want, then using it in a vlookup:

Please Login or Register  to view this content.

[akshay8530]

Hi,

I am looking for an function which can help me to select range of two col based on variable in Col A.

For Eg, B2 should have a formula which is selecting range D3:E8 by searching the variable name in Row 1

same way B3 should be F3:G8

The formula should not pull any date value in Col B, it should select the range.

Thanks

[Olly]

A formula can't select a range. It can use it, to do something else with, but can't select it. Your existing formula does nothing:

Please Login or Register  to view this content.

The range part of my formula above is:

Please Login or Register  to view this content.

If you just want to display the range address, you'll need a small UDF, something like:

Please Login or Register  to view this content.

So then, combine the two, and your formula in B2 becomes:

Please Login or Register  to view this content.

[cbatrody]

Alternatively, you can use the following formula in B2:

=ADDRESS(3,MATCH($A2,$A$1:$K$1,0))&":"&ADDRESS(8,MATCH($A2,$A$1:$K$1,0)+1)