[gaz173]

Hiya, I'm having a problem using a VLOOKUP.

It is a basic vlookup, though the cell that it is looking up and the range it is looking in, is made up of completely different formulaes, though the end-value of these formulaes are the same.

The problem I am having is when I input the VLOOKUP formula (it is correct) then Excel is trying to match up the formula in the cell, rather than the end-value that the formula outputs.

I've heard there is a way (using VBcode) to force excel to read the end-value in a cell instead of the formula when using a VLOOKUP.

Thanks in advance,

 

[dubya008]

could you post your formula?

 

[gaz173]

Basically in 'Race Data'!C3 I have the fastest time for a race, and in 'Rider Data'!B3:B40 I have the list of racers, along with their personal times in 'Rider Data'!I3:I40.

The formula I was using is;

=VLOOKUP(C3,'Rider Data'!B3:I40,1)

The actual spreadsheet can be found here - http://www.gazza-online.net/ict/Prototype.xls

Thanks, :lol:

 

[The Villan]

It shouldn't do that.

You may need to add False at the end of the Vlookup formula. If your lookup column is not in numerical order then you will have a problem getting the result. By adding false in the vlookup formula, it makes Excel go completely down the lookup column until it finds a match even if the nuumbers aren't in numerical order.

=VLOOKUP(A2,G2:H6,2,FALSE)

Is that what your problem is? if not come back with an uploaded version of your spreadsheet.

 

[gaz173]

Sorry I've tried it with false at the end, to no avail.

 

[The Villan]

oops a cross posting. Posted mine not knwoing the 2 post above were there.

Anyway cant load your example

 

[etaf]

i think all you need to do is sort the list and put the value of I3 into A3

and new formula
=VLOOKUP(C3,'Rider Data'!A3:B40,2)

this now looks in C£ to get the time
then looks at A and B = times and Names = sorted by Time

and returns the name


V lookup looks at the leftmost colume of your arrary and compares to the value you want and then offset by the number of columns you have set to lookk up

 

[etaf]

heres the spreadsheet

 

[The Villan]

Beat me to it Etaf - well done I will let you get on with it

 

[etaf]

hi "the Villian" no need to go - different timezones etc - post in whenever you like

 

[The Villan]

Lets see how Gaz gets on with your answer etaf. I have to go anyway as i have to put my daughter to bed. Will look in later

 

[The Villan]

Oh I am in Lincolnshire so I could almost have nipped over to Gaz's place LOL

 

[gaz173]

What do you mean sort the list and move I into A?

I can't sort it because it would mess up the names, and I cant copy any values over because I need them to update automagically when I edit the 'race times input'! sheet.

Hey its never too late to pop over and do it :up: lol

 

[gaz173]

Forget that, I've found a solution from mr.excel. Basically for anyone else the formula I was given was:

=INDEX('Rider Data'!B3:I40,MATCH('Race Data'!C3,'Rider Data'!I3:I40,0),1)

Reasoning being:

vlookup() does 'right-lookups' - i.e. it requires the lookup value to be the leftmost column of the lookup range & returns something from a matching row in a column to the right. to do left lookups (and other things), use index / match combinations.

Though I need to edit the formula so that it works for cells C3 to C11, though when I change the C3 it gives me the #N/A! error. However changing it to D3 (which if you look at my spreadsheet would be for slowest times) it works just fine!

Anymore help would be appreciated, lol

 

[Zack Barresse]

Hi there,

You would have received a viable solution had you properly posted your requirements and data structure in their entirety. The members here are very good, as good as those at MrExcel and even other boards around the interweb. The problem is generally the OP (Original Poster). No offense, it is just hard to read minds.

 

[gaz173]

Oh, ok. Sorry for the inconvenience? I know the people here are good, i've experienced problems before where they have found a solution for me.

 

[Zack Barresse]

Quality members here, that is for sure!!

 

[The Villan]

Just got back.

Unfortunately didn't get time to look at the spreadsheet properly, but Index is a good way. When I had a quick look, I thought what the hell is gaz trying to do with Vlookkup.The layout didn't look good.

Whenever I did lookup tables, I always made sure I pulled the data I needed with use of formulas into a table of its own and then did my lookup based on that.

Very often the big problem is spreadsheet design. Too often people make their spreadsheet without any thought as to how they will achieve their end result requirements.

Anyway Gaz,you seemed to have worked things out for yourself which is good.

In future can you upload your spreadsheet through the process that techguy allows you and not through a website.

Good luck with it.

 

[gaz173]

Sorry dude, but yea, I took notice of the dude on mrexcel about the way vlookups work, and did your way of shifting everything into 1 table, and as the vlookup reads from the left to the right, made sure the column I was looking up was on the far left.

Thanks for your help guys, I'll mark this thread as solved.

Talk to you after the weekend, when I run into another problem

 

[etaf]

see post #7

V lookup looks at the leftmost colume of your arrary and compares to the value you want and then offset by the number of columns you have set to lookk up

so by putting the entries into A3 using the expression =I3 this keeps your formulas and allows the vlookup to work

by sorting the whole section - does that muck up anything - sort by I so you have now can use vlookup - using the formula I posted
have a look at the spreadsheet i posted to.