Need help vlookup and a migraine. Help with vlookup may help with migraine.
Best Answers
-
Hey @imtonydillard
An Index/Match is one approach that will work for you.
=INDEX({sheet 2 Risk column}, MATCH(Carrier@row, {sheet 2 Carrier column},0))
(if you want to hard code in "Aetna MA" , replace the Carrier@row with this. As written, the lookup is dynamic based on what is in the Carrier column, per row, of Sheet1)
You will have to create the cross sheet references yourself, you cannot simply copy paste the formula.
When you create the cross sheet references (from the formula window), the name of the ranges will be different than what I have written in the formula above. This is expected as smartsheet generically assigns the names. You can change these - it is a good practice.
Does this work for your smartsheet? And for the migraine? 😁
Kelly
-
Great! As a point of interest, depending on your sheet, the Index/Match can be a more robust formula and help with sheet performance since you are not carrying an entire 'table' in the formula. Once you need a formula with multiple criteria, the Index/Match easily transitions to the Index/Collect. A vLookup cannot manage multiple criteria. This is why I chose the Index/Match approach.
I'm glad you got the formula to work for you.
cheers
Kelly
Answers
-
Hey @imtonydillard
An Index/Match is one approach that will work for you.
=INDEX({sheet 2 Risk column}, MATCH(Carrier@row, {sheet 2 Carrier column},0))
(if you want to hard code in "Aetna MA" , replace the Carrier@row with this. As written, the lookup is dynamic based on what is in the Carrier column, per row, of Sheet1)
You will have to create the cross sheet references yourself, you cannot simply copy paste the formula.
When you create the cross sheet references (from the formula window), the name of the ranges will be different than what I have written in the formula above. This is expected as smartsheet generically assigns the names. You can change these - it is a good practice.
Does this work for your smartsheet? And for the migraine? 😁
Kelly
-
@Kelly Moore Mega thanks for your help on this. It worked for my on the first try and because of your guidance I was able to figure out how to write a vlookup formula to do the same.
=INDEX({sheet 2 Risk column}, MATCH(Carrier@row, {sheet 2 Carrier column},0))
=VLOOKUP(Carrier@row, {CCIndex Repository Range 1}, 13, false)
-
Great! As a point of interest, depending on your sheet, the Index/Match can be a more robust formula and help with sheet performance since you are not carrying an entire 'table' in the formula. Once you need a formula with multiple criteria, the Index/Match easily transitions to the Index/Collect. A vLookup cannot manage multiple criteria. This is why I chose the Index/Match approach.
I'm glad you got the formula to work for you.
cheers
Kelly
-
@Kelly Moore is the 2022's most recommended pain reliever for migraines caused by smartsheets.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!