Solved

# VLookup

Posted on 2012-04-12
306 Views
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
Question by:ExpExchHelp
• 5
• 3
• 2
• +2

LVL 13

Assisted Solution

Shanan212 earned 100 total points
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

Saqib Husain, Syed earned 400 total points
Try

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

LVL 12

Expert Comment

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

=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

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

Saqib Husain, Syed earned 400 total points
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

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

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

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

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

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

PERFECT!!!   Thanks again, ssaqibh.
0

## Join & Write a Comment Already a member? Login.

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.

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!