VLOOKUP return return the next populated cell above

[Exceladd1ct]

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:

1a	1
1b
2a	2
3a	3
3b
3c

<tbody>
</tbody>

Is there a way to return 3 when i am looking for 3c?

=VLOOKUP(C1,'C:\Myfiles\[file.xlsx]Sheet1'!$A$1:$F$19,2,0)

Thank you!

 

[Yongle]

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]

Yes, the table above is for illustration purposes. In reality i am trying to lookup numerical values, 4-7 characters length.

 

[Yongle]

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]

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 for	Result	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​	Value	Animal	c	d	e	f
2​	9991012​	eel	xx	xx	xx	xx
3​	1012999​	dog	xx	xx	xx	xx
4​	101299​	cat	xx	xx	xx	xx
5​	101299t	bat	xx	xx	xx	xx
6​	1012​	ant	xx	xx	xx	xx
7​	etc ...

Sheet: Sheet1