Look up functions for multiple cells and matching

Question

I am trying to lookup a value when two columns are the same. To start, I have a drop down menu. The users selects which item name they want and when they click it, it enters into cell H26. Next, I have a table. There are 4 columns : NAME, LOGIC, TITLE to DISPLAY, VLOOKUP.

Cells E39-E43is the NAME column. So pretty much when the user select from the drop down menu an item list those being Modules 1-7, Modules 8-12, etc., the label gets inserted into cell H26. The Logic column has an IF statement that will either put “1-7”, “8-12”, etc. or leave it blank.

I am using the Title to Display column to reference. Below is the table. I am trying to use the vlookup column and in one cell be able to lookup and match when a cell in the Logic column matches the cell in the Title to display. When a match occurs, I need the value, whether its 1-7, 8-12, 13-16 etc. Can this be accomplished using one cell and with the least complicated logic. The front end program doesn’t respond well to perplexed and elongated excel functions. Thank you for your time enter image description here

So if the first cell in LOGIC is 1-7, you want 1-7 in the VLOOKUP column. If 17-13 is in the 4th cell, you want 17-23 in the VLOOKUP column. If all cells are blank in LOGIC, you want a blank. If there are more than one cell in LOGIC that matches TITLE to Display, you want the first match. LOGIC cannot contain something else than blank that doesn't match TITLE to Display. Are all those right? — Jerry
Thanks for your response, there wont be more than one cell in LOGIC that matches. Since the drop down menu selects only one option, h26 has only one value. This means that the LOGIC column is either all blank or it has one field that has the values indicated in the IF statement. I am merely trying to grab whatever that displays in the LOGIC column. Did this answer what you are inquiring about in your response? Thanks — ENGR024

Jerry Jerry · Accepted Answer · 2014-05-21T20:27:02

Well, as per what you mentioned, you can actually use a simple VLOOKUP formula with an approximate match lookup mode:

=VLOOKUP("zz",F39:F42,1)

I'm assuming that your range of LOGIC is within F39:F42. The above formula normally returns only the last non-blank text value (at least, one that is alphabetically before zz) from the given range. The above is equivalent to:

=VLOOKUP("zz",F39:F42,1,TRUE)

OR

=VLOOKUP("zz",F39:F42,1,1)

They are the same thing, but when you omit the last parameter, excel defaults to True (or 1)

EDIT: I overlooked the fact that there won't actually be blanks in the range F39:F42, but empty strings left behind from the IF. For small number of rows, you can concatenate all the strings together:

=F39&F40&F41&F42

For larger sets of data, you might have to resort to an array formula:

=INDEX(F39:F42, MATCH(1,--(F39:F42<>""),0))

Invoked with Ctrl+Shift+Enter

Look up functions for multiple cells and matching

2 Answers