If/then relating to a legend

[Closed]
Report
Posts
1
Registration date
Tuesday September 17, 2013
Status
Member
Last seen
September 17, 2013
-
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
-
This must be easier than I'm thinking it.
I have a set of data I collect every month that I drop into an excel. It's in a list. I take that list and pivot into other views. What I'm looking to do is have certain words infer other words.

I have a Legend:
Legend Specialty
ALVARADO Pediatrician
BISHOP Family
BLACHE Pediatrician
BRADY Internist
BRAUHN, M Surgeon
CAMPAGNA Pain

This list keeps growing. How do I paste Alvaro and have Pediatrician autofill the following cell? Right now the formula is HUGE and incomplete because my list keeps growing.

See for yourself - =IF([@DR]=$M$4,$N$4,IF([@DR]=$M$5,$N$5,IF([@DR]=$M$6,$N$6,IF([@DR]=$M$7,$N$7,IF([@DR]=$M$8,$N$8,IF([@DR]=$M$9,$N$9,IF([@DR]=$M$10,$N$10,IF([@DR]=$M$11,$N$11,IF([@DR]=$M$12,$N$12,IF([@DR]=$M$13,$N$13,IF([@DR]=$M$14,$N$14,IF([@DR]=$M$15,$N$15,IF([@DR]=$M$16,$N$16,IF([@DR]=$M$17,$N$17,IF([@DR]=$M$18,$N$18,IF([@DR]=$M$19,$N$19,IF([@DR]=$M$20,$N$20,IF([@DR]=$M$21,$N$21,IF([@DR]=$M$22,$N$22,IF([@DR]=$M$23,$N$23,IF([@DR]=$M$24,$N$24,IF([@DR]=$M$25,$N$25,IF([@DR]=$M$26,$N$26,IF([@DR]=$M$27,$N$27,IF([@DR]=$M$28,$N$28,IF([@DR]=$M$29,$N$29,IF([@DR]=$M$30,$N$30,IF([@DR]=$M$31,$N$31,IF([@DR]=$M$32,$N$32,IF([@DR]=$M$33,$N$33,IF([@DR]=$M$34,$N$34,IF([@DR]=$M$35,$N$35,IF([@DR]=$M$36,$N$36,IF([@DR]=$M$37,$N$37,IF([@DR]=$M$38,$N$38,IF([@DR]=$M$39,$N$39,IF([@DR]=$M$40,$N$40,IF([@DR]=$M$41,$N$41,IF([@DR]=$M$42,$N$42,IF([@DR]=$M$43,$N$43,IF([@DR]=$M$44,$N$44,IF([@DR]=$M$45,$N$45,IF([@DR]=$M$46,$N$46, 0)))))))))))))))))))))))))))))))))))))))))))

Can anyone help??

Thanks,

Kat

1 reply

Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
KatsKratch , Good afternoon.

I believe that you can use a VLOOKUP function to automate your job.

If you show us more about your data and layout I'm sure that you'll receive a lot of answers.

Attach here a little model of it and put there what are your data and how you desire the result.