Trying to find of HLookup alternative formula. 2

[kuldeps]

HI All,

I am have been a excel VBA programmer from last couple of years, but not an expert in excel formulae. Thought of taking your expert help!!

My question :

I have a situation where I have to lookup the field say emp grade in a column match it against list of grades from another range and return the correnponding pakage from another range. I cannot use Hlookups or vlookups as the columns are not static. Any pointers would be really a great help.

Please check the attache sample sheet.

Grade Package Grades Associate Manager Sr Manager Deputy Sr. Deputy
Associate
Associate
Manager
Sr Manager
Deputy
Sr. Deputy Rank 100 101 102 103 104
Package A A+ B B+ D

 

[SkipVought]

hi,

as the columns are not static

Well do the NAMES of the columns change? I mean does Deputy change to some other spelling?

If not, the HLOOKUP will find the correct column for referece in a VLOOKUP function, for instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kuldeps]

HI Skip,

No column names dosent change however , rows can no can change, hence I am looking for solution without using Hlookups.


Regards,
Kuldeps.

 

[SkipVought]

[tt]
Grade Associate Manager
Associate 1111 2222
Manager 3333 4444
[/tt]
[tt]
=VLOOKUP("Associate",$A$1:$C$3,Match("Manager",$A$1:$C$1,0),FALSE)
[/tt]
No matter what column or row the value is in, you will receive the correct value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kuldeps]

Hi,

Thanks for the response!! To make it more generic , I am trying lookign for solution without using Vlookup or Hlookups.

Please check the sample file , apologies , guess I am not clear in my requirement.

http://www.mediafire.com/file/d3ytdmaam1v/SAMPLE1.xls

Regards,

 

[SkipVought]

OK Use ONE example.

What are the givens?

What is the expected result?

Exaplain the MANUAL step by step process to get from GIVENS to RESULT.

Pleae answer all three questions completely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kuldeps]

Thanks!!

I have modified excel to clarify all questions.

Please check the attached sample1.xls in which col D is static and col E is expected output with col inputs are J77 and J1414.

http://www.mediafire.com/file/d3ytdmaam1v/SAMPLE1.xls

thanks a lot for ur time again!!

 

[kuldeps]

Guess, I have managed to solve this by using Index, Match....



=INDEX($J$14:$P$14,MATCH("Package",$J$14:$P$14,0),MATCH("Associate",$K$7:$P$7,0))

Please let me know if you feel there might be better solution.


Thanks a lot for all your help!!

 

[Gruuuu]

That's no more or less generic than VLOOKUP and/or HLOOKUP. What specifically will change? Do the "Grades" on row 7 move to a different row? Do the "Packages" on row 14 change rows? Do the grades switch order?

I'm really not sure what you're trying to avoid by not using vlookup. The ONLY benefit I know of to using index/match is when your target data is not to the right of or below the lookup value.

 

[SkipVought]

I generally prefer using INDEX and MATCH rather than V or H lookup, as 1) I almost ALWAYS use column named ranges and 2) the lookup column can be ANYWHERE in the table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Agreed, Index/Match is superior. I don't really understand the purpose of having V/H Lookup.
Regardless, looking at his spreadsheet, I don't see a reason here why Index/Match is going to fix the problem where V/H Lookup does not.
Anyway, Kuldeps. I'm pretty sure that with creatively dynamic named ranges, offsets, and Index/Match, you would be able to overcome whatever dynamic data format you have. The problem is figuring out how it is dynamic (what changes?) and then how to plan for that.

 

[SkipVought]

Really, if you're ONLY doing lookup (as opposed to aggregations with SUMPRODUCT) a range from whereever to THE LAST ROW/COLUMN would work just as well in INDEX/MATCH spreadsheet functions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kuldeps]

thanks a lot for ur help!! Just that I had some bad experiences with V & H lookups and also the rows and colms are not fiexd and can change dynamically, hence I wanted not to use lookups.