Solved

VLookup

Posted on 2012-04-12
12
306 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
Comment Utility
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
Comment Utility
Try

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

Expert Comment

by:FarWest
Comment Utility
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
 
LVL 9

Expert Comment

by:anthonymellorfca
Comment Utility
=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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 9

Expert Comment

by:anthonymellorfca
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
... 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
Comment Utility
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
Comment Utility
PERFECT!!!   Thanks again, ssaqibh.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now