Need to understand Index/Match and Vlookup can be nested.

Topic

New Reply

Hello,
Below is the final results of what I need: The Begin and Ending Balance for 3 Fund Numbers that have different codes.

I would key in the Fund Number and Code in Spreadsheet 1. I need a formula linked to a second spreadsheet named “Download” to apply the appropriate Beg/End Balance for each 4 Digit Code.

Here’s the formula I used..which didn’t work
=VLOOKUP($A19,MATCH(B19,’ Download 4607′!$B$6:$K$664,0),6,FALSE)

Fund Number code Beg Bal End Balance
541705 2677 0.00 0.00
541705 2061 384,912.00 212,068.56
541705 2161 309,652.00 29,533.48

Download
Fund CODE Beg Bal End Balance
541705 2677 0.00 0.00
541705 2061 384,912.00 212,068.56
541705 2161 309,652.00 29,533.48

Reply | Quote

Replies

I realized I can combine the two columns (Fund + code) making it a unique value and then use VLookup.

Reply | Quote

Yes, you have to do it that way.

see attached example file.

The most efficient way is to use MATCH and INDEX rather than VLOOKUP.
Note that once you have matched the row number in the Download, you can use it to retrieve data from any column.
So it is used twice: onece to get the Beg Bal, and then again for the End Balance.

Using this method is far more efficient than using VLOOKUP for each value you want to return.

zeddy

Reply | Quote