Advanced Lookup, Match and Merge Feature

[d79k74]

Hey Everyone,

I got some amazing help from someone on the forum a few minutes ago and no I'm am trying to do the 2nd part of the project.

I'll try to explain what I'm trying to do, but I have attached the same spreadsheet so you can see what i'm talking about.

If you open the spreadsheet, I want the following to happen:

I want to do a search with Column I & J, if it finds the name in column B & C then insert the Age Group in 1 column and the Gender in another one.

I hope this makes sense

thanks

[Bob Phillips]

Put this array formula in G2 and copy down and across.

=IF(ISNA(MATCH($B2&$C2,$I$2:$I$11&$J$2:$J$11,0)),"",INDEX(K$2:K$11,MATCH($B2&$C2,$I$2:$I$11&$J$2:$J$11,0)))

[DGagnon]

you will need to add a concatinated list to your lookup, so for example, add a new row between J and K and put this formula

=I2&J2

then in G you can do a lookup like this

=VLOOKUP(B2&C2,K:L,2,0)

[DGagnon]

or do what Bob suggested

[d79k74]

Hi Bob, I tried to highlight cell G2 and copy and paste the formula and I get an error saying that there is something wrong with the formula.

am I doing something wrong?

[DGagnon]

looks like the forurms were randomly adding spaces to bobs formula... i think what he was going for (and probably pasted) is this:

=IF(ISNA(MATCH($B2&$C2,$I$2:$I$11&$J$2:$J$11,0)), "", INDEX(K$2:K$11, MATCH($B2&$C2,$I$2:$I$11&$J$2:$J$11,0)))

i added spaces to prevent the board from doing it in undesired locations

[d79k74]

Thanks for your quick response, but once I highlight G2 cell and put the formula in, I don't get the same error but I get the following (#VALUE!) on the cell and all the cells under it when I drag the + to copy the functions the the cells below it.

Am I doing something wrong?

[DGagnon]

it is an array formula, so you will need to confirm it using Ctrl+Shift+Enter.

if done correctly you will see the formula surounded by { }

[d79k74]

Sorry I don't understand, so how do I add the formula? it's not just a copy and paste?

[DGagnon]

you can paste the formula into the formula bar, and then instead of pressing enter, press Ctrl+Shift+Enter, and it will change the way Excel runs the formula to Array.

[d79k74]

aha, that worked, it added the the value to the age group, but can i also add the gender to a column next to it?

Thx

[DGagnon]

just need to change it like this:

=IF(ISNA(MATCH($B2&$C2,$I$2:$I$11&$J$2:$J$11,0)), "", INDEX(L$2:L$11, MATCH($B2&$C2,$I$2:$I$11&$J$2:$J$11,0)))

[d79k74]

Ok that seemed to work, the only issue im having now is that on the original attachment there were only only row with names on the right side, I actually have a total of 91 names that I need to do this to.

I tried changing each # that was 11 on the formula to 91 and it didn't work.

What am I missing?

[DGagnon]

you just need to extend the range of 2 and 11 to 2 and 91 or what ever your range is.

[d79k74]

I wanted to add a another column for an email address and phone #'s, I tried to do the following formula where P$ what the Email and I got the following error

"Microsoft Excel Cannot Calculate Formula, there is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the UNDO command."

Any ideas why this is happening?

=IF(ISNA(MATCH($B2&$C2,$I$2:$I$91&$J$2:$J$91,0)), "", INDEX(P$2:P$91, MATCH($B2&$C2,$I$2:$I$91&$J$2:$J$91,0)))

[DGagnon]

its hard to tell without seeing your workbook... but it appears something is creating a circular reference there.

[d79k74]

Ok,

Take a look at the spreadsheet and let me know what im doing wrong

I hope this helps

thanks

[DGagnon]

this formula should work... there is already a formula in I, make sure you erase that one first

=IF(ISNA(MATCH($B2&$C2,$K$2:$K$91&$L$2:$L$91,0)), "", INDEX(O$2:O$91, MATCH($B2&$C2,$K$2:$K$91&$L$2:$L$91,0)))

[d79k74]

That worked thanks