Solved

VLookup

Posted on 2012-04-12
12
312 Views
Last Modified: 2012-06-21
Experts:

Please see attached XLS... how can I modify the existing VLookup formula and apply it to the survey.

Details are in the XLS.

Thanks,
EEH
VLookup.xls
0
Comment
Question by:ExpExchHelp
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 100 total points
ID: 37837833
It wouldnt be vlookup, it would be

On L21 enter this and drag it down

=IF(G21<>"",1,IF(H21<>"",2,IF(I21<>"",3,IF(J21<>"",4,5))))
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 400 total points
ID: 37837912
Try

=MATCH("*",G21:K21,0)
0
 
LVL 12

Expert Comment

by:FarWest
ID: 37838102
there is another way to do it, if I understand what you finally need,
check pivot table in sheet1, no need for vlookup or coded values
VLookup2.xls
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 37838158
=MATCH("x",G21:K21)

this gives 1 2 3 4 or 5 as desired

copied down in the place of each hard typed number from M21 to M29.

seems to be all you need if all you want to do is derive the number associated with the heading.

You could change the headings as often as you like and the numeric answer will always be correct.

EDIT:  ssaqibh beat me to it above, sorry missed that - and his is wild card based so any input response will be used.


Anthony
0
 

Author Comment

by:ExpExchHelp
ID: 37838568
Shanan212:  It works... a bit cumbersome though.  8)

ssaqibh / Anthony:  How exactly the MATCH works?   Is it that first column with a value gets the "1"... next one gets the "2"?     If so, what if the scoring was reversed and Strongly Disagree needs a "5" (vs. currently a "1")?

fryezz:  Thanks for the pivot table.   That's not what I was after though.


Any additional thoughts on this?   Thx!

EEH
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 400 total points
ID: 37838641
Yes the match simply returns the position.

To be able to change the order of the column titles try

=VLOOKUP(INDEX($G$20:$K$20,1,MATCH("*",G21:K21,0)),$D$3:$E$7,2,0)
0
 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 37838688
hmm, I originally was using match inside a choose, until I realised in your example it didn't matter,

so=CHOOSE(MATCH("x",G21:K21),1,2,3,4,5)

In the above the choose is doing the same as match but if you switched it then,

=CHOOSE(MATCH("x",G21:K21),5,4,3,2,1)

gives the opposite and of course you can put anything you like in the CHOOSE result positions, including cell references.

or then again simpler to exactly match your suggestion of "if reversed":

=6-MATCH("*",G21:K21,0)

so
6-5=1
6-4=2
6-3=3
6-2=4
6-1=5

MATCH gives position from a series of cells, that's the key. As long as what you do is position related, it will work for you.

How you want to manage entries that are not " x " or don't exactly match depends on the exact details inside the MATCH(details) formula - see Excel help for details.

Anthony
0
 

Author Comment

by:ExpExchHelp
ID: 37838948
Thanks to all.... several great solutions here.

I hope it's ok if I allocated points across the board (to give recognition to all who provided a workable or most effective solution).

Again, thanks!!

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 37839015
ssaqibh:

Oh forgot to ask... what if a cell was empty (i.e., I have an optional question/field).   How can I show "0" vs. "#N/A"?

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 37839080
... that is on the VLookup...

=VLOOKUP(INDEX($G$20:$K$20,1,MATCH("*",G21:K21,0)),$D$3:$E$7,2,0)

How can I show "0" vs. "#N/A" if empty?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37839116
Try

=if(isna(MATCH("*",G21:K21,0)),"".VLOOKUP(INDEX($G$20:$K$20,1,MATCH("*",G21:K21,0)),$D$3:$E$7,2,0))
=if(isna(MATCH("*",G21:K21,0)),0.VLOOKUP(INDEX($G$20:$K$20,1,MATCH("*",G21:K21,0)),$D$3:$E$7,2,0))
0
 

Author Comment

by:ExpExchHelp
ID: 37839221
PERFECT!!!   Thanks again, ssaqibh.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question