Returning LOOKUP value from same column, different row.

[chilli76]

Hi Guys

Just about got my head around VLOOKUP function when searching different columns but I now need to return a value 2 rows below the reference cell on another sheet.

Eg 'Entry 2' CF3 is reference cell and I need to find the matching cell in Column A 'Prices' sheet and return the value 2 rows below that.

Anyone? Please!

Cheers

[Pete_UK]

What formula are you using at the moment?

Pete

[chilli76]

TBH none as I'm not sure how to lookup a row instead of a column.

After a quick google search I found an answer to a similar problem but obviously that is unique to that persons sheet.

ie =IF(ISNA(INDEX('King Box'!E:E,MATCH(A2,'King Box'!$A$1:$A$1000,0)-1,1)),INDEX('King Matt'!E:E,MATCH(A2,'King Matt'!$A$1:$A$1000,0)-1,1),INDEX('King Box'!E:E,MATCH(A2,'King Box'!$A$1:$A$1000,0)-1,1))

Apparently this returns a value 1 row below the matched cell but god knows how I apply it to my circumstances.

[Pete_UK]

I don't know the name of the sheets you are using, which cells to look in, which columns to return from, etc., so I thought if you posted your formula then I could work these things out for myself. Ignore the 2 rows down for the moment, and tell me what formula you would use if you just wanted a straight VLOOKUP - I will convert it into an INDEX/MATCH for you and add 2 onto the MATCH term.

Pete

[chilli76]

Just noticed also that the CF3 reference cell is slightly different that the lookup value cell in that the cell i'm looking to match the reference cell has the time added on the end so it needs to be an approximate match and not exact. If that makes sense?

[chilli76]

OK

VLOOKUP(CF3,'Prices'.A1:B500,2,0)

If I was simply looking to return a value in a different column.

Got to shott now but thatnks for your help and I'll take a look when I get back.

Much apreciated

[Pete_UK]

Okay, try this instead:

=INDEX('Prices'!B:B,MATCH(CF3,'Prices'!A:A,1)+2)

The 1 at the end of the MATCH term will look for an approximate match, but if you have several values which have the same date but a different time then it might give inconsistent results.

Hope this helps.

Pete

[chilli76]

Hi Pete

Just seem to be getting error message with that formula.

I've attached a simplified sheet for you to look at if you wouldn't mind and I've colour coded what values need to be in which cells from the prices sheet into Entry 2.

Much appreciated

[Glenn Kennedy]

Hi. the INDEX formula is simple enough. The BIG problem is that theatch identifier on the two sheets are ENTIRELY inconsistent. If they were identical

=INDEX(Prices!$A:$A,MATCH('Entry 2'!$CF3,Prices!$A:$A,0)+2)

works (and make the 2 6 and 10 for the other two columns). However, there is no obvious pattern in the way that th ematch ID is constructed in prices. is there a pattern/rule and I just haven't spotted it??

[JohnTopley]

Following from Glenn's comments I recommend you change your data format: I have added "Sheet1" as a suggested layout.

Formula used is similar to Glenn's.

=INDEX(Sheet1!$A$2:$H$50,MATCH(1,(Sheet1!$A$2:$A$50=$C3)*(Sheet1!$B$2:$B$50=$D3),0),4)

This has to be entered as Ctrl+Shift+Enter

[chilli76]

Hi Glen

The data on the prices sheet is from a web query and I'm trying to make it fit my sheet if I can. Every time I run the web query the data positioning will change so the only identifier I've got is what I have in CF3 and trying to match that.

[JohnTopley]

OK - maybe it is possible to reformat the data from the Web query to make it more usable (?)

[chilli76]

Hi John

Yes looks like I'm going to have to reformat the incoming data to make it more usable. I'll see what I can come up with.

Thanks for all your help guys

Cheers

[chilli76]

Hi

I'm going to use a lookup table to convert the team names eg Newcastle to Newcastle United but does anyone have any ideas on what formula I can use to put NewcastlevArsenal12:45 into 2 separate cells, Newcastle and Arsenal. It needs to be generic and apply to all possible team combinations as the data positioning will change every week.

Cheers

[JohnTopley]

You can get the time by =RIGHT(A1,5) assuming all times are hh:mm



To get the names you could use =FIND("v",A1) to get the position of v (might be problem with Aston Villa!)

Then use = LEFT(A1,Find(.....)-1) to get the "home" team

To get away team use =TRIM(MID(a1,Find(,,..)+1,40)) but this will leave the Time on the end! the 40 is arbitrary i.e. choose a figure long enough to get the team.

I am off out now: I'll give it some thought later if you are still suck.

[JohnTopley]

Formulae:

Home team

=LEFT(A84,FIND("v",A84)-1)

Away team

=MID(A84,FIND("v",A84)+1,LEN(A84)-FIND("v",A84)-1-5)

Time

=TEXT(RIGHT(A84,6),"hh:mm")

How are you progressing?

[JohnTopley]

See the attached with macro which completes the "Entry 2" form :

(A) Matches are in alphabetical (home team) order
(B) Assumes match-related data is in consistent format. Data before or after is ignored

Please Login or Register  to view this content.

.

[chilli76]

Hi John

Only just got back to it this morning so I'll have a look see at your fantastic offerings. Before I saw your attachment I'd managed to remove the time from the end of the game into one cell and then I was going to carry on with your suggestion of FIND "v" but I'm just looking at your sheet now.

Thanks very much

[chilli76]

Hi John

I am soooo close!

I've used a slight variant on the formulas you posted earlier.......

Formulae:

Home team

=LEFT(A84,FIND("v",A84)-1)

Away team

=MID(A84,FIND("v",A84)+1,LEN(A84)-FIND("v",A84)-1-5)

Time

=TEXT(RIGHT(A84,6),"hh:mm")


which work great BUT..........going back to an earlier comment regarding Aston Villa there seems to be an issue with the "v". Aston Villa is actually fine as the V is capitalised but Liverpool and Wolverhampton Wanderers throw out #N/A's. Can you think of any way round this as it is now my only obstacle to fully automating my sheet.

Great work on the Macro by the way. I am going to use this as a way for me to learn how to include this type of thing in all my sheets, it would be a great addition.

Thanks again for all your help.

[JohnTopley]

I don't think there is answer to the FIND issue but another option is based on the formula below:

=MATCH("*" & VLOOKUP(C3,$CQ$1:$CR$21,2,0) & "*",Prices!$A$2:$A400,0)


CQ1:CR21 contains the "Long Name" & " short Name" (I just added the "Short name" to the end of my existing table) so substitutes "Newcastlle United" with "Newcastle" and searches column A of "PRICES" for a match.

It finds the "match record" i.e "NewcastlevArsenal15:00".

If you prefixed it with INDEX(Prices!Prices!$A$2:$A400,MATCH (.....as above)+ n) where n is the offset to get the H,D,A data then you can work off the data in "Entry 2" (n=2,6,10 respectively for the data in columns "H","D",and "A"

=INDEX(Prices!$A$2:$A400,MATCH("*" & VLOOKUP($C3,$CQ$1:$CR$21,2,0) & "*",Prices!$A$2:$A400,0)+2) for "H"

Hope this helps.

[chilli76]

Okay so the above formula seems to work fine with the teams that remain the same but throws out N/A's for the names that change. Is it a lookup table problem?

[JohnTopley]

You didn't read my note properly nor copied the formula!

You need to "reverse" the lookup to take the "long name" and return "short name" with VLOOKUP: see the table in CP to CR.

See attached.

[chilli76]

Ahhhh I see. I did wonder why the lookup was referencing empty cells. My head hurts now though to be honest.

Thanks again.

[JohnTopley]

Don't worry : I frequently get that feeling.

The advantage of the macro version is that the whole process is "automated" so with a new web query you will immediately create your "Entry 2" chart.

Enough work for now: it's a weekend!