Lookup Functions

[Steves73]

Hi All

Im have a brain freeze on excel, been looking at this for 2 days now

For the life of me I can’t find a function that looks columns B,D,F and H

Matches the date and auto populates the corresponding date in the calendar Columns K to AO with data from Column C,E,G,and I

Example is

LV-102 – Function looks at the date in Column B, copies data in C5, paste to AD4
LV-106 - Function looks at the date in Column B, copies data in C9 pastes it to X4
LV-106 - Function looks at the date in Column H, copies data in I9 pastes to K8

And so on

Please help

 

[James006]

Hi,
Have you tested

=INDEX(A1:J50,MATCH("LV-102",$A:$A,0),MATCH("5000",$3:$3,0))

 

[Steves73]

Hi,
Have you tested

=INDEX(A1:J50,MATCH("LV-102",$A:$A,0),MATCH("5000",$3:$3,0))

Hi James

Yes, Index match (That I can see) will only work if selecting LV-102. I need a function that looks at the entire range and any LV number with a date in Column B,D,F and H then copies the adjacent data (LV-106-5000 etc into the calendar. So don't think Index Match will work

Thanks

 

[James006]

Hi again,
You could have LV-102 replaced by LEFT(AD5,6)
and 5000 replaced by RIGHT(AD5,4)

 

[Peter_SSs]

What Excel version are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, since we cannot copy from an image to test, a much better idea for sample data (and expected results) is XL2BB

 

[James006]

Re,

To be precise for the second portion extracted from the right, you should test

RIGHT(AD5,LEN(AD5)-FIND(CHAR(1),SUBSTITUTE(AD5,"-",CHAR(1),2)))

 

[Steves73]

Hi Guys, thanks for the suggestions, maybe I didn't explain it very clearly and or maybe its a Marco that has to do it

Im hoping the below will help try and explain

 

[Peter_SSs]

thanks for the suggestions,

You did not address either of my suggestions, so I am no closer to being able to offer anything.

What Excel version are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, since we cannot copy from an image to test, a much better idea for sample data (and expected results) is XL2BB

 

[James006]

Hi again,

You are right ... a Macro would be a safe solution ...

 

[Steves73]

Thanks again guys.

Would anyone out there have a macro that could do this?