Drag formula question

[Spraggs]

Evening All,
Am I missing something here ?

I've got a simple VLOOKUP in cell B2...
=VLOOKUP($A2,Sheet4!$A$2:$BB$92,2,FALSE)

I now want to drag this formula across to column BB and then down to row 135.
The LOOKUP value is always column A (row to suit Table array) but the column index number needs to change with formula.

Q. How can I do this ?

Many Thanks Jase

 

[Jonmo1]

Try

=INDEX(Sheet4!B$2:B$92,MATCH($A2,Sheet4!$A$2:$A$92,0))


Hope that helps.

 

[Spraggs]

Jonmo 1,
That's great, just one issue....
How do I avoid #N/A ?

When I use VLOOKUP I get away with like...
Example.
=IF(ISNA(VLOOKUP(C4,Sheet1,2,FALSE)),"", VLOOKUP(C4,Sheet1,2,FALSE))

Thanks

 

[Jonmo1]

The same way...
But it's easier with Index/Match, you only have to test the Match part, not the whole formula...

Try

=If(ISNA(MATCH($A2,Sheet4!$A$2:$A$92,0)),"",INDEX(Sheet4!B$2:B$92,MATCH($A2,Sheet4!$A$2:$A$92,0)))

 

[Jonmo1]

Actually, for performance purposes since this formula is being repeated so many times...

Put the match in an available column (say column BC)

In BC2 and filled down put
=MATCH($A2,Sheet4!$A$2:$A$92,0))

Then your formula in B2 and Filled Right/Down is

=If(ISNA($BC2),"",INDEX(Sheet4!B$2:B$92,$BC2))

This will reduce the amount of calculations tremendousely.

 

[Spraggs]

Superb,
Thank you very much.

 

[Jonmo1]

Glad to help, thanks for the feedback.