Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VLookup

Posted on 2012-04-12
12
Medium Priority
?
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 400 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 1600 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
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!

 
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 1600 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

604 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