Hi I was wondering, how do I create a reference table that matches two different database?
for example (in the attachment also) : If I have one database with stock name and stock number and the second database got stock name and stock ticker how do I match between the two, so If I have Microsoft stock it will know to return either the ticker or the number?
You can see that in cell I9 it returns me Microsoft though the ticker belongs to Dell.
I'd appreciate your help
Thanks in advance
Udi
Hi and welcome to the board.
Haven't got XL here so I can't do anything in your sheet, but maybe you should have a look at the Vlookup function
Cheers
you're on the right track, but remember match returns the row from the range specified. so if you use match "microsoft "in range f:f it will return 5
if you use it in range f3:f5 it will return 3 you can adjust for this, if using a whole range by subtracting rows above your start point
using on your sheet -2
=MATCH(A1,F:F,0)-2 thus making it give the result from f3:fXXXXX i.e 3(match can only look at one column or row)
the index function has to be in format
array,row number,column number
the array can be anything contiguous in your case e3:f5 the column numbers will then be 1,2 or 3
you can put the match formula in
like this
array/match formula/column
so to get result from "microsoft" say in a1
use in cell b1
=INDEX(E3:F5,MATCH(A1,F:F,0)-2,1) to return MSFT
AND in c1
=INDEX(H3:I5,MATCH(A1,I:I,0)-2,1)
to return
555333
a better explanation is given here
http://www.mrexcel.com/tip021.shtml
Last edited by martindwilson; 07-07-2008 at 08:01 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks