VLookup

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
ExpExchHelpAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shanan212Commented:
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
Saqib Husain, SyedEngineerCommented:
Try

=MATCH("*",G21:K21,0)
0
FarWestCommented:
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Anthony MellorChartered AccountantCommented:
=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
ExpExchHelpAnalystAuthor Commented:
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
Saqib Husain, SyedEngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony MellorChartered AccountantCommented:
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
ExpExchHelpAnalystAuthor Commented:
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
ExpExchHelpAnalystAuthor Commented:
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
ExpExchHelpAnalystAuthor Commented:
... 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
Saqib Husain, SyedEngineerCommented:
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
ExpExchHelpAnalystAuthor Commented:
PERFECT!!!   Thanks again, ssaqibh.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.