• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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
0
ExpExchHelp
Asked:
ExpExchHelp
  • 5
  • 3
  • 2
  • +2
3 Solutions
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
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
 
ExpExchHelpAuthor 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
 
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
 
ExpExchHelpAuthor 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
 
ExpExchHelpAuthor 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
 
ExpExchHelpAuthor 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
 
ExpExchHelpAuthor Commented:
PERFECT!!!   Thanks again, ssaqibh.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now