index searching non-value/text

[hwkeyser]

I have a very long function i'm trying to develop. it has two parts, but i only need help on the first:

I am trying to index search a column based on matching a row. However, i'm trying to match a non-value item. i am trying to match a word.

for example:
i want to return a value from column A by finding the first instance of a word in column D.

Initially, i was trying index/match searches, but because my 'lookup-value' is a not a value, it does not allow this function.

(original formula)

=INDEX('S11'!D:D,MATCH(F4,'S11'!H:H,0))

sections in red are words


Is there a vlookup/index style function which allows the use of non-value cells?

this spreadsheet has 7000 rows and hundreds of word variables i'm searching for, additionally, i'm trying to apply this function to lookup all cells with no exact words placed in quotes within the function so i can duplicate it down 600 instances.


any help would be great, thanks!

-HK

 

[VoG]

INDEX/MATCH should work fine with text values. What problem do you have?

 

[hwkeyser]

It always returns the basic #N/A


I have about 50 documents i use index/match on, but this is the first i couldn't figure out.

 

[T. Valko]

I have a very long function i'm trying to develop. it has two parts, but i only need help on the first:

I am trying to index search a column based on matching a row. However, i'm trying to match a non-value item. i am trying to match a word.

for example:
i want to return a value from column A by finding the first instance of a word in column D.

Initially, i was trying index/match searches, but because my 'lookup-value' is a not a value, it does not allow this function.

(original formula)

=INDEX('S11'!D:D,MATCH(F4,'S11'!H:H,0))

sections in red are words


Is there a vlookup/index style function which allows the use of non-value cells?

this spreadsheet has 7000 rows and hundreds of word variables i'm searching for, additionally, i'm trying to apply this function to lookup all cells with no exact words placed in quotes within the function so i can duplicate it down 600 instances.


any help would be great, thanks!

-HK

That formula works just as well with text.

Book1
	D	H	I	J	K
2	1	Tom	_	Biff	4
3	2	Sue	_	_	_
4	3	Lisa	_	_	_
5	4	Biff	_	_	_
6	5	Joe	_	_	_
Sheet1

This formula entered in K2:

=INDEX(D2:D6,MATCH(J2,H2:H6,0))

If your formula isn't working then one possibility is that the lookup values don't "exactly" match the string(s) you're looking for.

See this for common data problems:

http://contextures.com/xlFunctions02.html#Trouble

 

[hwkeyser]

for some reason when i change the match from exact to greater-than, it returns the correct value from the final instance of the word.

any explanation for such a peculiar circumstance?

 

[T. Valko]

for some reason when i change the match from exact to greater-than, it returns the correct value from the final instance of the word.

any explanation for such a peculiar circumstance?

I'm sure there's a valid explanation but it would require being able to see the file data to figure it out.

Or, it may be just "dumb luck". That happens every now and then!