Index and Match function across 2 spread sheets

[joe_brown14]

Hi all,

I was wondering if anybody could help me?

i am trying to understand the index and match function to look up a value from one tab and insert this into another,

I have 2 sheets - "Reactive" and "AFP"

on the reactive sheet i need a figure from the AFP sheet, in column b of the reactive sheet i have a set of job numbers which i need to lookup on the AFP sheet to find the value in column D of the AFP Sheet to populate column AJ in my reactive sheet(Job numbers in column A and the value in Column D of the AFP sheet)

I have tried to do this but its beating me sadly!

Can anybody help?

thanks

joe

 

[wideboydixon]

You don't have to use INDEX/MATCH since the lookup value is to the left of your required value; you could use VLOOKUP. Assuming you have headers, put this in AJ2 of your "Reactive" sheet:

=VLOOKUP($B2,AFP!$A:$D,4,FALSE)

WBD

 

[joe_brown14]

I did try this first however my excel isn't allowing me to do this when i drag the value down the page it returns the same value the whole way down. I have googled this but nothing works
therefore i thought i would try and learn the index and match function to see if that would work instead.

 

[wideboydixon]

There's no reason this wouldn't work when dragging it down. Does the row number increase in the formula when you drag it down? Can you post any sample data?

WBD