Vlookup/Hlookup Alternative

[TransMogrifier]

Is there an alternative function that performs/mimics the job done by Vlookup/hlookup?

thanks for your help

 

[VoG]

Take a look at INDEX/MATCH http://www.contextures.com/xlFunctions03.html

 

[TransMogrifier]

Hi VoG,

Thanks for your help........it works perfectly........ and another question is it better to use Index/Match combo instead of Vlookup/hlookup??

 

[VoG]

It can be more efficient. Plus INDEX/MATCH can lookup to the left which VLOOKUP cannot.

 

[TransMogrifier]

Also I was wondering if it would be possible to use "IF" along with logical operators "AND" and "OR" for the same purpose as V/hlookup?

Thanks

 

[VoG]

Try giving a specific example.

 

[TransMogrifier]

I dont have a specific example , I was asking more in a general vein. Just was wondering if it would be possible i.e. if I have a list of things and wanted to find a specific name then instead of Vlookup/hlookup I can instead use IF statement along with And/or/not logic

Thanks

 

[VoG]

Maybe like this

Excel Workbook
	A	B	C	D
1	K	69	97	A
2	J	3	88
3	A	97
4	B	34
5	O	48
6	I	35
7	V	80
8	M	54
9	E	16
10	L	47
Sheet2

 

[TransMogrifier]

Well so as this example that you have showed I will have to use Index/Match combined with If to replicate vlookup/hlookup functionality......and if there is a string that I need to find Istext replaces Isnumber rite.....?

 

[VoG]

No, it is always ISNUMBER. MATCH will return a number of #N/A if no match is found.