LinkBack URL
About LinkBacks
I want to look up a corresponding value for a list of values, from multiple sheets as database. (see attached example)
Index/Match doesn't seem to want to look across multiple sheets.
I want to look up a corresponding value for a list of values, from multiple sheets as database. (see attached example)
Index/Match doesn't seem to want to look across multiple sheets.
1) List the sheet names to search. I listed them in K1:K3 (Sheet2, Sheet3, Sheet4)
2) Put this array formula in D6:
=INDEX(INDIRECT("'" & INDEX($K$1:$K$3, MATCH(TRUE, COUNTIF(INDIRECT("'" & $K$1:$K$3 & "'!G:G"), C6)>0, 0)) & "'!F:F"), MATCH(C6, INDIRECT("'" & INDEX($K$1:$K$3, MATCH(TRUE, COUNTIF(INDIRECT("'" & $K$1:$K$3 & "'!G:G"), C6)>0, 0)) & "'!G:G"), 0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
That first formula results in #N/A because that code isn't found on the 3 searched sheets.
3) Copy D6 down.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Doesn't seem to work for some reason
I followed your instructions but it seems to run into trouble with the first countif/indirect statement
You misspelled the sheet names.
Doh! Thanks for that
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks