Looking up a value in a defined table

[michellepace]

Hi there,

I have two tables, TRADES and SPECS. From within my TRADE table I have this formula which looks up a value from a column in SPECS called "Margin":

=VLOOKUP([@Future],SPECS,10,FALSE)

Of course "Margin" is the 10th column in in my SPECS table... however, to make this forumula (or another if need be) more intuative to somebody reading it, I'd like to do something like this:

=VLOOKUP([@Future],SPECS,margin,FALSE)

Anybody have a solution how I can achieve something in using something more readable along these lines rather than just "10"?

Thanks in advance,
Michelle

[michellepace]

The point being... in my SPECS table whilst I never rename columns, I often add or delete columns. If I have a static number, like 10, defined in my formula then I have to be very careful how columns are added to SPECS....

[Cdubisms]

Can you post the workbook?

[FDibbins]

Hi Maichael hoe gaan dit, en waar in SA is jy? (SA ex-pat here from PE and Klerksdorp)

I would probably do this with INDEX/MATCH instead.

Seeing as you "add and delete columns" not sure you could still use a "word" to ID the column, but perhaps you could use MATCH() in a cell on it's own to create the column number, then give that a range name? This should work using VLOOKUP, too

[Cdubisms]

FDibbins, couldn't she just refer to the column name in the formula?

So if the column name is Margin, wouldn't =VLOOKUP([@Future],SPECS,[Margin],FALSE) work?

That's what I had in my head anyway, and I wanted to try it out if she were to post an example.

[FDibbins]

FDibbins, couldn't she just refer to the column name in the formula?

So if the column name is Margin, wouldn't =VLOOKUP([@Future],SPECS,[Margin],FALSE) work?

That's what I had in my head anyway, and I wanted to try it out if she were to post an example.

Good point. I dont like structured tables, so I try to avoid them - I need to change that view.

Still, My approach should also work, but yours may be better

[michellepace]

Hi FDibbins and Cdubisms, thank-you both for your replies.

FDibbins, ek is goed. Maar we run out of water and electricity so ek is bly jy is in 'n ander plek! The borewors is still nice though. =)

Cdubisms, your answer sounds exactly what I want and what made sense to me. Basically I'm trying to make my formula as intuitive and easy to understand as possible. However the formula you give does not seem to work.

I've posted a work book for you both to show me what you mean. I've got a separate column for each of you =)

I can't seem to attach the file to this thread, so I've posted it here for you: http://www.michellepace.com/Table_lo...out_index.xlsx

Thanks again,
Michelle

[silambarasan.J]

Hi.

Please find attached the file.please check and confirm.

[michellepace]

Thank-you silambarasan, but your solution does not solve my question. Thanks for trying anyhow.

[xlnitwit]

Hi Michelle,

I think that, with structured referencing, an INDEX and MATCH approach may be the simplest:

=INDEX(SPECS[Margin],MATCH([@Future],SPECS[Future],0))

[michellepace]

Hi xlnitwit,

That's perfect! Thank-you

Michelle

[xlnitwit]

You are most welcome.