CODEWITHSUNDEEP
excelvbaexcel-formula
motodev
motodev

Reputation: 61

Getting cell reference from a text value in a data table in excel

I'm trying to use the COLUMN function with VLOOKUP nested inside like so:

COLUMN(VLOOKUP(A2,ws2!$C$2:$E$80,3,FALSE))

but this isn't working as expected. I'm guessing this is because the Vlookup function returns the text value but Column function requires a cell reference.

Is there a way to get the cell reference from the lookup value?

I cannot use Match function since it only can handle 1 dimensional array but the lookup value can be from any row or column in the data table.

Upvotes: 0

Views: 240

Answers (3)

DjC
DjC

Reputation: 1292

Since you've tagged VBA, you could use a very simple UDF with the Range.Find method:

Option Explicit

Public Function LookupAddress(lookup_value As String, lookup_range As Range, Optional match_case As Boolean) As String
    LookupAddress = lookup_range.Find(lookup_value, LookAt:=xlWhole, MatchCase:=match_case).Address
End Function

Then, to return the cell address of the matching value (not case-sensitive), use:

=LookupAddress(A2, ws2!$C$2:$E$80)

Or, for a case-sensitive match, use:

=LookupAddress(A2, ws2!$C$2:$E$80, TRUE)

You could also add error handling to the function, in the event a match is not found; however, it's just as easy to use the existing IFERROR() function:

=IFERROR(LookupAddress(A2, ws2!$C$2:$E$80, TRUE), "No match")

There are other options you can also mess around with, such as using LookAt:=xlPart to find a partial match, SearchOrder:=xlByColumns to search by columns instead of rows, or SearchDirection:=xlPrevious to search from bottom-to-top, right-to-left. However, the basic UDF outlined above should meet your needs.

NOTE: If your intention was to lookup a value in one column, then return the address of the corresponding value in another column, an additional col_offset parameter could be used with Range.Find.Offset.Address.

Cheers!

Upvotes: 0

P.b
P.b

Reputation: 11578

VLOOKUP starts from a given column and returns a value in a referenced column n columns to the right starting from the start column = 1.

So =VLOOKUP(A2,B2:C10,2,FALSE) returns the value in column C (column B =1, column C =2) where the first found value in column A (start column of A2:C10) equals the value of A2.

If we replaced 2 with 1 it'd return the value in column A (=1) where the value in that column matches the value of A20.

If we replaced 2 with 3 it would return an error, because the range we refer to consists of 2 columns, so referencing the 3rd is not possible. You would have to expand the range to =VLOOKUP(A2,B2:D10,2,FALSE).

That having said, to lookup a column number of a value within a 2D range, VLOOKUP will not work.

You could use IF or REPT in combination with TEXTJOIN like this:

=TEXTJOIN(", ",1,TRANSPOSE(IF($C$2:$G$5=$A2, COLUMN($C$2:$G$5),"")))

or

=TEXTJOIN(", ",,REPT(COLUMN($C$2:$E$4),$C$2:$E$4=$A2))

Both versions combine the column numbers (comma separated) in case multiple values are found in the range: enter image description here

Upvotes: 1

vbakim
vbakim

Reputation: 621

Yes, COLUMN function requires a cell reference, and the VLOOKUP function returns a value, not a cell reference.

Try below function: It will get the column number of the first occurrence of the value in cell A2 within the range ws2!$C$2:$E$80 and returns that column number. If not found, return #N/A.

=MIN(IF(ws2!$C$2:$E$80=A2, COLUMN(ws2!$C$2:$E$80)-COLUMN(ws2!$C$2)+1))

Upvotes: 1

Related Questions