VLOOKUP return return the next populated cell above

Exceladd1ct

New Member
Joined
Feb 10, 2019
Messages
49
Hello, i am using VLOOKUP to return values from a table from another workbook. But the table is organized is such way that some cells may be empty:
1a1
1b
2a2
3a3
3b
3c

<tbody>
</tbody>
Is there a way to return 3 when i am looking for 3c?
Code:
=VLOOKUP(C1,'C:\Myfiles\[file.xlsx]Sheet1'!$A$1:$F$19,2,0)
Thank you!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,771
Office Version
  1. 365
Platform
  1. Windows
Are those values oversimplified for illustration?
- are you always trying to match a 1 or 2 character string which is always a single digit numeric + single alpha character?
- does the lookup range contain a mixture of 1 and 2 character strings?
 

Exceladd1ct

New Member
Joined
Feb 10, 2019
Messages
49
Yes, the table above is for illustration purposes. In reality i am trying to lookup numerical values, 4-7 characters length.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,771
Office Version
  1. 365
Platform
  1. Windows
In that case, my first thought is a non-starter :(

Please provide some more typical of examples of
a) what is being looked up
b) acceptable matches in the event of the actual match not being found

You originally said are you looking for an alpha-numeric string (3c)
- if the alpha characters are removed from EVERY search string would the correct match ALWAYS be the remaining numeric value?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,771
Office Version
  1. 365
Platform
  1. Windows
How about this method? Does it return the values you want? Can it handle your range of values?

If a match is not found for a number followed by an single alpha character then the alpha character is ignored and a match sought for the number itself
The value being looked up AND values in the lookup range can be EITHER totally numeric OR numeric followed by single alpha character

=VLOOKUP(IF(ISNUMBER(A2),A2,IF(COUNTIF(Sheet1!A1:A19,A2)>0,A2,VALUE(LEFT(A2,LEN(A2)-1)))),Sheet1!$A$1:$G$19,2,0)

Explanation of formula:
An error is returned if a match is not found for EITHER cell value OR cell value minus last alpha

IF A2 contains a number then
- VLOOKUP(A2 value)
ELSE A2 is alpha-numeric
- test with COUNTIF to see if alpha-numeric value is in the lookup range
IF COUNTIF returns 0 then
- remove last character and VLOOKUP(A2 minus last alpha)
ELSE
- VLOOKUP(A2 alpha-numeric value)

RESULTS TABLE

Excel 2016 (Windows) 32 bit
A
B
C
1
Look forResult Formula
2
1012c ant =VLOOKUP(IF(ISNUMBER(A2),A2,IF(COUNTIF(Sheet1!$A$1:$A$19,A2)>0,A2,VALUE(LEFT(A2,LEN(A2)-1)))),Sheet1!A$1:$F$19,2,0)
3
1012​
ant =VLOOKUP(IF(ISNUMBER(A3),A3,IF(COUNTIF(Sheet1!$A$1:$A$19,A3)>0,A3,VALUE(LEFT(A3,LEN(A3)-1)))),Sheet1!A$1:$F$19,2,0)
4
9991012​
eel =VLOOKUP(IF(ISNUMBER(A4),A4,IF(COUNTIF(Sheet1!$A$1:$A$19,A4)>0,A4,VALUE(LEFT(A4,LEN(A4)-1)))),Sheet1!A$1:$F$19,2,0)
5
101299​
cat =VLOOKUP(IF(ISNUMBER(A5),A5,IF(COUNTIF(Sheet1!$A$1:$A$19,A5)>0,A5,VALUE(LEFT(A5,LEN(A5)-1)))),Sheet1!A$1:$F$19,2,0)
6
101299s cat =VLOOKUP(IF(ISNUMBER(A6),A6,IF(COUNTIF(Sheet1!$A$1:$A$19,A6)>0,A6,VALUE(LEFT(A6,LEN(A6)-1)))),Sheet1!A$1:$F$19,2,0)
Sheet: Sheet2

LOKKUP TABLE

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
1
ValueAnimalcdef
2
9991012​
eelxxxxxxxx
3
1012999​
dogxxxxxxxx
4
101299​
catxxxxxxxx
5
101299t batxxxxxxxx
6
1012​
antxxxxxxxx
7
etc ...
Sheet: Sheet1
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,226
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top