[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

How do I reverse the columns in a Named Range?

I have several Named Ranges of Text/Numerical lists in a Lookups tab as the following template:

  FieldName  FieldName_id
     Grass               1
    Weeds               2
   Poppies              3
    Reeds                4

In other tabs which reference the text values in dropdown lists (via Data Validation). On closing the file I ise VBA wipe out the dropdown lists and use temporary VLookups to replace the text with their number values in the various tabs so they can be scraped into an SQL database. I figured that part out - so far so good. (BTW - I'm not developing the database, just the Excel portion.)

The problem is that when the file is reopened I need to change those numerical values back to their text equivalents, but of course I can't use the same VLookup functions because a VLookkup can't look at a colmn to the left of the value you're looking up. All the solutions I can come up with are klunky: a Reverse Lookups tab linking to the main Lookups tab but with the columns switched, a third column after the range linking to the text value in the third column, etc.

There has to be something better!

I'd love to use VBA to take an existing range and create a new range, but with the columns switched as follows:

  FieldName_id  FieldName
          1                  Grass
          2                  Weeds
          3                 Poppies
          4                  Reeds

Do my VLookup on this virtual range an move on!

Anybody go an answer?
Thanks!
0
monbois
Asked:
monbois
  • 8
  • 4
  • 3
  • +1
1 Solution
 
redmondbCommented:
monbois,

It's not what you asked for, but there is a straightforward equivalent of a "Reverse VLookUp" which you might find useful. For example, suppose you are looking up B2:B12 and want to return the value in column A then...
=offset($A$1,match("What I'm looking for",$B$2:$B$12,0),0)

Open in new window

Regards,
Brian.
0
 
redmondbCommented:
monbois,

Or if you prefer...
=INDEX($A$2:$A$12,MATCH("What I'm looking for",$B$2:$B$12,0),0)

Open in new window

Reards,
Brian.
0
 
broro183Commented:
nice one RedmondB :-)

I prefer your second option as the Index function is less volatile than Offset (Monbois, you can see Charles Williams' page for more details: http://www.decisionmodels.com/calcsecretsi.htm ).

Monbois,
RedmondB's second option can be made even more flexible if your lookup table has multiple columns...
I occasionally use the below format, either in conjunction with a validation list (see below) or to return info from a number of columns that are included in a lookup table:
=INDEX($A$2:$e$6,MATCH($F$1,$B$2:$B$6,0),MATCH($F$2,$A$2:$E$2,0))

Open in new window

where:
"$A$2:$e$6" is the full dataset, including headers
"$F$1" is a separate cell that can be updated with "What I'm looking for"
"$B$2:$B$6" is the column in the lookup table that includes "What I'm looking for".
"$F$2" is a separate cell in the header row that has the same title as the desired column in the lookup table (this cell can be converted into a validation list, so you can easily cycle through the results from each of the headers as desired by changing a dropdown).
"$a$2:$e$2" is the header row of the lookup table that is checked & matched against cell F2.

hth
Rob
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
monboisAuthor Commented:
Thanks for your suggestions, Brian, but I'm afraid they're not much help. There's no switch out one value for anotehr value, that I can see. A VLookup requires referencing at least 2 columns to work, and your solution only references 1 (Col. B). I can't even get your solution to work.

Using VBA, I'm placing the VLookup formula in Col. Z of Tab A. It's looking at what's in Col. B and then finding that same value in Col. A of Tab B (my Lookups tab) and returning the value in Col B. of Tab B.

So, text value 'Grass' returns numerical value 1 in VLookup. That value, 1, is then copied and pasted over 'Grass' in Col. A of Tab A.

To reverse it, I need to look up 1 in Col. B of Tab B and return it's text value in Col. A of Tab B - Grass.

If you could display exactly how your formulas do that, I'd be thrilled! But like I said, I don't see where they can cross-reference multiple columns to return a different value.

Thanks.
0
 
redmondbCommented:
Monbois,

My formulas had two references - one in column A the other in column B.

Please see attached sample. Formula is...
=INDEX($A$2:$A$6,MATCH(E2,$B$2:$B$6,0),0)

Open in new window

Regards,
Brian
0
 
redmondbCommented:
0
 
monboisAuthor Commented:
Brian - Thanks for the Excel file. I clearly see what  you're formula's doing now. Unfortunately I can't have dual Text/Number columns in both my single-column original value and the VLookup source. As I wrote, I can use a plain VLookup function to find the numerical value of a text string and then replace that string with the number. Reversing that is the real crux, though.

Rob - I still haven't quite figured out your solution, but unfortunately there's just no way I can have matching headers in my columns, which you say is what $F$2 is for.

Thanks.
0
 
monboisAuthor Commented:
Brian & Rob - I figured it out!

Fiddling around with the formula example Brian sent me, the following works great!

Col. A                  Return            Col. G      Col. H
1                  Grass            Grass      1
2                  Hay            Hay      2
3                  Flowers            Flowers      3
4                  Cows            Cows      4
5                  Sheep            Sheep      5

Here's the tweeked formula:

=INDEX($G$13:$G$17,MATCH(A13,$H$13:$H$17,0),0)

I think (hope) I can handle it from here, but if you have anything else you think might be even better, feel free to share.

Thanks again!
0
 
redmondbCommented:
monbois,

Sorry that apparently didn't help. Could you post a sample file (a couple of dummy records would be enough), please?

Thanks,
Brian.
0
 
redmondbCommented:
monbois,

Oops, crossing posts. I'm glad it worked, however, if you need any further improvements, the sample might still be useful!

Regards,
Brian
0
 
broro183Commented:
hi guys, I'm off to bed now but I think Brian has this completely under control :-)

Monbois, Please see the attached file for examples of the suggestions I made in my previous post. I have changed it slightly, so that what was $F$2, can now be considered to be any of the cells from H2:k2 or M2. I don't know what exactly what you are trying to do. Can you please post an example file with your layout, an example of "before" & "after" and some explanations?

Rob Reverse-Lookup-v2.xls
0
 
monboisAuthor Commented:
Brian,

I don't know if you sent your message above before I sent my thanks but it posted after, but attached is your original file with the formula adjusted for my use, allowing me to have a single-column value needing a reversed-VLookup equivalent.

Thanks Again,

Frank
Reverse-Lookup.xls
0
 
broro183Commented:
Ooopps a crossed post from me too :-)

btw, I hope you were already thinking this way, but in case you aren't... I think all the points should go to Brian.

Rob
0
 
redmondbCommented:
Thanks, Rob!

Frank,
Thanks for the file. I'm afraid I don't have a clear enough understanding of your process to suggest any improvements from just that.

Regards,
Brian.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
redmondbCommented:
Thanks, teylyn.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now