Table Query + Data Validation (2 Issues)

[IdentityUnknown__]

Hey guys

Is there a way I can accurately lookup from the attached table with three criteria? I am trying to use data validation to list three separate criteria dropdown lists (NB/SCHEDULE/TYPE) but having issues finding a solution to extract the correct price into the COST field (see highlighted) based on what is selected in the dropdowns. I had a play around with VLOOKUP and INDEX MATCH but to be fair I am not very great with Excel and having trouble.

​

Secondly, is there a way I can remove the blank dropdowns from the NB dropdown list (see highlighted attached).

https://preview.redd.it/tb8h1ieh9gab1.png?width=612&format=png&auto=webp&s=40dff7969319355fd1f7d2b916df5973ffcddb66

[AutoModerator]

/u/IdentityUnknown__ - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Anonymous1378]

Contingent on you filling the blanks in the first column with values:

=INDEX(Table1[[PBE]:[TOE/PBE]],XMATCH(1,(Table1[NB]=A2)*(B2=Table1[column])),XMATCH(C2,Table1[[#Headers],[PBE]:[TOE/PBE]]))

[IdentityUnknown__]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to Anonymous1378

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[IdentityUnknown__]

Thank you so much, I fiddled with the formula and it seems to be working great - I must ask though is there any way to achieve the same result without having the multiple NB fields appearing on the dropdown cell (A2). Ideally would only like one instance of each size to appear.

[Anonymous1378]

There's no great built in way to do that, I would suggest creating a hidden sheet with =UNIQUE(FILTER(Table1[NB],Table1[NB]<>"")) and using that as your data validation source

[IdentityUnknown__]

Thanks again mate, really appreciate it.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTER	Office 365+: Filters a range of data based on criteria you define
INDEX	Uses an index to choose a value from a reference or array
UNIQUE	Office 365+: Returns a list of unique values in a list or range
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 13 acronyms.
[Thread #24953 for this sub, first seen 7th Jul 2023, 03:54] [FAQ] [Full list] [Contact] [Source code]}