Populating a value based on a series of data

**DawnG1231** · 03-30-2023, 12:11 PM

NOTE: I have added a spreadsheet for a clearer understanding of my question.

I would like to create a spreadsheet where I can populate a value based on a series of table selections.

For example:

If I choose:

Option A from Table 1,
Option A from Table 2,
Option B from Table 3,

Then ______ populates.

Would my best option be to make a unique ID for each individual series and then populate a value, or is there some other (easier) way to make this happen?

EDITED TO ADD: I showed this on the spreadsheet but did not spell it out in my original question, the type of selection for each coverage may change depending on the person. In other words, a person can choose individual coverage for one insurance, family for another, and employee/children only for another so it's not as clear cut as just one option. That being said, I'm not opposed to a drop-down for each insurance type (healthcare/denta/vision) since this may change year to year.

Spreadsheet attached for real reference.

**Limor_OP** · 03-30-2023, 12:46 PM

Hi,
first you need to make sure you use the same expression in your result table to match the same in the source tables.
I changed it wherever it didnt match completely and added another column for "Type" to split it from "Employee Type"

E26 and copies to other cells:
=INDEX($D$13:$H$20,MATCH(C$25&$B26&D26,$A$13:$A$20&$B$13:$B$20&$C$13:$C$20,0),MATCH(C26,$D$12:$H$12,0))

**DawnG1231** · 03-30-2023, 12:53 PM

This is fantastic - could you be so kind as to break this down for me on the index and match? I've not used it before.

**Limor_OP** · 03-30-2023, 01:26 PM

Here's another option without the Type column addition.

=INDEX($D$13:$H$20,IFERROR(MATCH(C$25&$B26&MID(C26,SEARCH("/",C26)+1,99),$A$13:$A$20&$B$13:$B$20&$C$13:$C$20,0),MATCH(C$25&$B26,$A$13:$A$20&$B$13:$B$20,0)),MATCH(IFERROR(LEFT(C26,SEARCH("/",C26)-1),C26),$D$12:$H$12,0))

as for the index the index/match function ,
the structure of the formula is INDEX(array, row_number, column_number)

The range I took is the table of values, the row was aimed to columns A,B&C combined, and the column is determined by the match between the value in your column C, and the set of valued in line 12.

You can read more about these combined functions here:
https://exceljet.net/articles/index-and-match

Populating a value based on a series of data

LinkBack

Thread Tools

Rate This Thread

Display

Populating a value based on a series of data

Re: Populating a value based on a series of data

Re: Populating a value based on a series of data

Re: Populating a value based on a series of data

Thread Information

Users Browsing this Thread

Similar Threads

Auto Populating a series of tabs within a spreadsheet

Need help in Auto populating data based on value

Populating multiple cells based on data

Populating Data Based on Two Criteria

Populating a series of cells based on a worksheet change event

Populating information in a series of Tabs

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions